{"text_format":"1","workbook":"xlsx","n_sheets":16,"sheets":["Introduction","Step 1 - Project Information","Step 2 - Project Type","Test","Communication","Comm","DMS","DMS Calc","CCTV","CCTV Calc","TRAWS","TRAWS Calc","Other","PARAMETERS","REFERENCES","DROP-DOWNS"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":3,"defined_names":16,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":26,"n_cols":10,"rows_kept":26,"cut":false,"hidden":false,"merged":[[21,2,21,3],[22,2,24,3],[13,2,14,3],[2,3,2,6],[5,2,5,9],[6,2,6,9],[4,2,4,9],[7,2,7,9],[8,2,8,9],[9,2,9,9],[13,5,13,9],[13,4,14,4],[15,2,16,3],[17,2,20,3]]}
[]
[null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process (TSMO-TIP)"]
[null,null,null,"Project Benefit-Cost Analysis"]
[]
[null,null,"Introduction:"]
[null,null,"The Project Benefit-Cost Analysis Tool has been developed to assess and quantify potential monetary benefits per project type. The Project Benefit-Cost Analysis Tool uses readily available, project specific data from the user and industry research to estimate potential project benefits. Project types include: Communication, DMS, CCTV Camera, Through Route Activated Warning System (TRAWS), and other. \n\nSeveral types of project benefits are considered for each project type: safety, mobility, productivity, and energy and environment. These estimated project benefits are then compared to the estimated total project cost. This methodology provides a clear and transparent manner in which to consider the value of a specific project, reducing concerns of inefficient use of funding resources.\n"]
[null,null,"User Instruction:"]
[null,null,"Step 1 - Complete Project Information. \nStep 2 - Select the Project Type you are proposing. \nStep 3 - Perform Project Benefit-Cost Analysis by providing project specific responses for each of the questions as appropriate. \n*It should be noted that this analysis is a generalized, estimated approach to considering potential monetary benefits. It is expected that some responses will be estimates based on the users knowledge of the project and location."]
[null,null,"User Resources:"]
[null,null,"The 'Data Needs' table below may be referenced to determine where each of the data requests may be obtained. Data sources include the Needs Analysis Tool, readily available information from the User, User estimated values, and asset management/maintenance information. For any questions or concerns, contact Todd Szymkowski (todd.szymkowski@dot.wi.gov)."]
[]
[null,null,"Project Benefit-Cost Analysis Data Needs"]
[]
[null,null,"Anticipated Data Source",null,"Data Need","Project Type"]
[null,null,null,null,null,"Communication Expansion","DMS","Camera","TRAWS","Other"]
[null,null,"User Response",null,"Project Specific Description","X","X","X","X","X"]
[null,null,null,null,"Deployment Intent",null,"X","X"]
[null,null,"Estimated",null,"Events per Year",null,"X","X"]
[null,null,null,null,"Average Event Duration",null,"X","X"]
[null,null,null,null,"Average Travel Time Savings ",null,"X","X"]
[null,null,null,null,"Outages per Year","X","X","X"]
[null,null,"Asset Management / Maintenance Reports",null,"Maintenance Tickets and Cost","X"]
[null,null,"TOPS Lab\nNeeds Analysis Tool*",null,"Crash Data by Type ","X","X","X","X"]
[null,null,null,null,"Average Vehicles Entering Intersection","X",null,null,"X"]
[null,null,null,null,"Average Daily Traffic Volumes Per Area",null,"X","X","X"]
[null,null,"*Needs Analysis Tool can be found here: https://transportal.cee.wisc.edu/gis/webmaps/tip"]

{"sheet":"Step 1 - Project Information","index":1,"n_rows":69,"n_cols":5,"rows_kept":69,"cut":false,"hidden":false,"merged":[[63,3,63,5],[65,2,65,5],[66,2,66,5],[68,2,68,5],[69,2,69,5],[53,3,53,5],[55,3,55,5],[57,3,57,5],[59,3,59,5],[61,3,61,5],[45,2,45,5],[47,3,47,5],[49,3,49,5],[51,3,51,5],[43,2,43,5],[37,2,37,5],[38,2,38,5],[40,2,40,5],[41,2,41,5],[34,5,35,5],[2,2,2,5],[9,2,9,5],[17,2,17,5],[18,2,18,5],[20,2,20,5],[4,4,4,5],[5,4,5,5],[6,4,6,5]]}
[]
[null,null,{"$f":"Introduction!D2","v":"Transportation System Management and Operations - Traffic Infrastructure Process (TSMO-TIP)"}]
[null,null,"Step 1 - Project Information"]
[]
[null,null,null,"Region:"]
[null,null,null,"Proposed Project Name:"]
[null,null,null,"Requested By:"]
[]
[]
[null,1,"Using each of the following Needs Analysis Tool presets, provide the anticipated level of need in the vicinity of the proposed project. Access the Needs Analysis Tool here: https://transportal.cee.wisc.edu/gis/webmaps/tip."]
[null,null,null,null,"Default TIP"]
[null,null,null,null,"Safety"]
[null,null,null,null,"Mobility (Present)"]
[null,null,null,null,"Mobility (Future)"]
[null,null,null,null,"Service"]
[null,null,null,null,"Freight Performance"]
[]
[null,2,"Briefly describe the proposed project. Please include the purpose and any relevant information."]
[]
[]
[null,3,"Identify any stakeholders who have been involved with the development of this project. "]
[]
[null,null,"¨","State Patrol","specify:"]
[]
[null,null,"¨","TOPS Lab (UW)","specify:"]
[]
[null,null,"¨","Project Team","specify:"]
[]
[null,null,"¨","Regional Stakeholders","specify:"]
[]
[null,null,"¨","BTO Stakeholders","specify:"]
[]
[null,null,"¨","Local Agencies","specify:"]
[]
[null,null,"¨","Other Agencies:","specify:"]
[]
[]
[null,4,"Briefly describe the outcome of the collaboration identified above."]
[]
[]
[null,5,"Please provide any further information that will be relevant when considering this project."]
[]
[]
[null,null,"Operations and Maintenance Considerations"]
[]
[null,6,"Indicate whether the following operations and maintenance items have been considered:"]
[]
[null,null,"¨","Who – Who are the stakeholders involved with the system?"]
[]
[null,null,"¨","What – What are the elements and the high-level capabilities of the system?"]
[]
[null,null,"¨","What – What existing networks will be affected by the system?"]
[]
[null,null,"¨","Where – What is the geographic and physical extent of the system?"]
[]
[null,null,"¨","When – What is the sequence of activities that will be performed?"]
[]
[null,null,"¨","Why – What is the problem or opportunity addressed by the system?"]
[]
[null,null,"¨","How – How will the system be developed?"]
[]
[null,null,"¨","How – How will the system be operated? Are there available resources to take on this responsibility or will additional resources be required? Will additional training be required?"]
[]
[null,null,"¨","How – How will the system be maintained? Are there available resources to take on this responsibility or will additional resources be required? "]
[]
[null,7,"Indicate any integration that will be required with this proposed deployment."]
[]
[]
[null,8,"Indicate any further information that will be helpful to document pertaining to operations and maintenance of the proposed deployment:"]

{"sheet":"Step 2 - Project Type","index":2,"n_rows":20,"n_cols":3,"rows_kept":20,"cut":false,"hidden":false,"merged":[[17,2,17,4],[19,2,19,4],[14,2,14,4],[12,2,12,4],[2,2,2,4],[4,2,4,4],[6,2,6,4],[8,2,8,4],[10,2,10,4]]}
[]
[null,null,{"$f":"Introduction!D2","v":"Transportation System Management and Operations - Traffic Infrastructure Process (TSMO-TIP)"}]
[null,null,"Step 2 - Project Type"]
[]
[null,null,"Communication"]
[]
[null,null,"DMS "]
[]
[null,null,"CCTV "]
[]
[null,null,"TRAWS"]
[]
[null,null,"Other"]
[]
[null,null,"Test"]
[]
[null,null,"Benefit-Cost Analysis Resources:"]
[null,null,"Analysis Parameters"]
[]
[null,null,"Research Resources"]

{"sheet":"Test","index":3,"n_rows":4,"n_cols":3,"rows_kept":4,"cut":false,"hidden":true,"merged":[[2,2,2,4]]}
[null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,"Project Benefits - Test "]
[]
[null,null,"Back"]

{"sheet":"Communication","index":4,"n_rows":101,"n_cols":11,"rows_kept":101,"cut":false,"hidden":true,"merged":[[10,4,10,9],[100,9,100,10],[38,4,38,9],[2,4,2,10],[7,8,7,9],[11,8,11,9],[5,4,5,7],[6,4,6,7],[7,4,7,7],[3,4,3,9],[5,8,5,9],[6,8,6,9],[13,4,13,9],[29,4,29,9],[35,4,35,9],[32,4,32,9],[23,4,23,9],[46,4,46,8],[52,4,52,8],[55,4,55,8],[49,4,49,8]]}
[]
[null,null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,null,"Project Benefit-Cost Analysis - Communication Infrastructure"]
[null,null,null,null,"Design-build and integrate fiber optic links between existing fiber infrastructure and signal systems, or procure and install cellular Ethernet modems. "]
[]
[null,null,null,null,"Region:",null,null,null,{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,null,"Proposed Project Name:",null,null,null,{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,null,"Requested By:",null,null,null,{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[]
[null,null,null,1,"What is the anticipated present value cost of the project?"]
[]
[]
[null,null,null,2,"Using each of the following Needs Analysis Tool presets, provide the anticipated level of need in the vicinity of the proposed project:"]
[null,null,null,null,null,null,null,null,"Default TIP",{"$f":"IF('Step 1 - Project Information'!F11=0,\"-\",'Step 1 - Project Information'!F11)","v":"-"}]
[null,null,null,null,null,null,null,null,"Safety",{"$f":"IF('Step 1 - Project Information'!F12=0,\"-\",'Step 1 - Project Information'!F12)","v":"-"}]
[null,null,null,null,null,null,null,null,"Mobility (Present)",{"$f":"IF('Step 1 - Project Information'!F13=0,\"-\",'Step 1 - Project Information'!F13)","v":"-"}]
[null,null,null,null,null,null,null,null,"Mobility (Future)",{"$f":"IF('Step 1 - Project Information'!F14=0,\"-\",'Step 1 - Project Information'!F14)","v":"-"}]
[null,null,null,null,null,null,null,null,"Service",{"$f":"IF('Step 1 - Project Information'!F15=0,\"-\",'Step 1 - Project Information'!F15)","v":"-"}]
[null,null,null,null,null,null,null,null,"Freight Performance",{"$f":"IF('Step 1 - Project Information'!F16=0,\"-\",'Step 1 - Project Information'!F16)","v":"-"}]
[]
[null,null,"Productivity Benefits"]
[]
[null,null,null,"P1. ","Will construction of this infrastructure eliminate the need to use currently leased fibers?"]
[]
[]
[null,null,null,null,"If so, how many fiber pairs are currently leased?"]
[null,null,null,null,null,null,null,null,null,"fiber pairs"]
[]
[null,null,null,"P2. ","How many device locations will be provided communication through this infrastructure?"]
[null,null,null,null,null,null,null,null,null,"locations"]
[]
[null,null,null,"P3. ","How many miles of fiber will be constructed with this project?"]
[null,null,null,null,null,null,null,null,null,"miles"]
[]
[null,null,null,"P4. ","Will this communication infrastructure be leased as a revenue source?"]
[]
[]
[null,null,null,"P5. ","If leased, how many fiber pairs are expected to be leased?"]
[null,null,null,null,null,null,null,null,null,"fiber pairs"]
[]
[null,null,null,null,null,null,null,"Estimated Annual Productivity Benefit:",{"$f":"Comm!J54","v":0}]
[]
[]
[null,null,"Redundancy Benefits"]
[]
[null,null,null,"R1.","Will this installation of communication infrastructure provide a redundant link?"]
[]
[]
[null,null,null,"R2.","Does this link provide redundancy to communication infrastructure that is leased to others?"]
[]
[]
[null,null,null,"R3.","Based on the portion of the network that is provided redundancy with this link, how would you rate the importance of this link?"]
[]
[]
[null,null,null,"R4.","Approximately how many outages are experienced per year that will benefit from this redundant link?"]
[null,null,null,null,null,null,null,null,null,"outages per year"]
[]
[null,null,null,null,null,null,null,"Estimated Annual Redundancy Benefit:",{"$f":"Comm!J89","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,null,"Y1","Annual Benefit",{"$f":"Comm!F94","v":0},null,"Y11","Annual Benefit",{"$f":"Comm!J94","v":0}]
[null,null,null,"Y1","Estimated Cost",{"$f":"Comm!F95","v":0},null,"Y11","Estimated Cost",{"$f":"Comm!J95","v":0}]
[]
[null,null,null,"Y2","PV Annual Benefit",{"$f":"Comm!F97","v":0},null,"Y12","PV Annual Benefit",{"$f":"Comm!J97","v":0}]
[null,null,null,"Y2","PV Estimated Cost",{"$f":"Comm!F98","v":0},null,"Y12","PV Estimated Cost",{"$f":"Comm!J98","v":0}]
[]
[null,null,null,"Y3","PV Annual Benefit",{"$f":"Comm!F100","v":0},null,"Y13","PV Annual Benefit",{"$f":"Comm!J100","v":0}]
[null,null,null,"Y3","PV Estimated Cost",{"$f":"Comm!F101","v":0},null,"Y13","PV Estimated Cost",{"$f":"Comm!J101","v":0}]
[]
[null,null,null,"Y4","PV Annual Benefit",{"$f":"Comm!F103","v":0},null,"Y14","PV Annual Benefit",{"$f":"Comm!J103","v":0}]
[null,null,null,"Y4","PV Estimated Cost",{"$f":"Comm!F104","v":0},null,"Y14","PV Estimated Cost",{"$f":"Comm!J104","v":0}]
[]
[null,null,null,"Y5","PV Annual Benefit",{"$f":"Comm!F106","v":0},null,"Y15","PV Annual Benefit",{"$f":"Comm!J106","v":0}]
[null,null,null,"Y5","PV Estimated Cost",{"$f":"Comm!F107","v":0},null,"Y15","PV Estimated Cost",{"$f":"Comm!J107","v":0}]
[]
[null,null,null,"Y6","PV Annual Benefit",{"$f":"Comm!F109","v":0},null,"Y16","PV Annual Benefit",{"$f":"Comm!J109","v":0}]
[null,null,null,"Y6","PV Estimated Cost",{"$f":"Comm!F110","v":0},null,"Y16","PV Estimated Cost",{"$f":"Comm!J110","v":0}]
[]
[null,null,null,"Y7","PV Annual Benefit",{"$f":"Comm!F112","v":0},null,"Y17","PV Annual Benefit",{"$f":"Comm!J112","v":0}]
[null,null,null,"Y7","PV Estimated Cost",{"$f":"Comm!F113","v":0},null,"Y17","PV Estimated Cost",{"$f":"Comm!J113","v":0}]
[]
[null,null,null,"Y8","PV Annual Benefit",{"$f":"Comm!F115","v":0},null,"Y18","PV Annual Benefit",{"$f":"Comm!J115","v":0}]
[null,null,null,"Y8","PV Estimated Cost",{"$f":"Comm!F116","v":0},null,"Y18","PV Estimated Cost",{"$f":"Comm!J116","v":0}]
[]
[null,null,null,"Y9","PV Annual Benefit",{"$f":"Comm!F118","v":0},null,"Y19","PV Annual Benefit",{"$f":"Comm!J118","v":0}]
[null,null,null,"Y9","PV Estimated Cost",{"$f":"Comm!F119","v":0},null,"Y19","PV Estimated Cost",{"$f":"Comm!J119","v":0}]
[]
[null,null,null,"Y10","PV Annual Benefit",{"$f":"Comm!F121","v":0},null,"Y20","PV Annual Benefit",{"$f":"Comm!J121","v":0}]
[null,null,null,"Y10","PV Estimated Cost",{"$f":"Comm!F122","v":0},null,"Y20","PV Estimated Cost",{"$f":"Comm!J122","v":0}]
[]
[]
[null,null,null,null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"Comm!I125","v":0}]
[null,null,null,null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"Comm!I126","v":0}]
[]
[null,null,null,null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"Comm!I128","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,null,null,null,"Return-on-Investment =",{"$f":"Comm!I129","v":{"$error":"#DIV/0!"}}]
[]
[null,null,null,null,null,null,null,null,null,"Back to Links Tab"]

{"sheet":"Comm","index":5,"n_rows":131,"n_cols":10,"rows_kept":131,"cut":false,"hidden":true,"merged":[[36,5,36,7],[49,4,49,7],[58,4,58,8],[65,4,65,8],[68,4,68,8],[40,4,40,8],[43,4,43,10],[46,4,46,10],[61,4,61,8],[62,4,62,8],[124,8,124,9],[125,8,125,9],[80,5,80,8],[82,5,82,8],[84,5,84,8],[2,4,2,10],[3,4,3,8],[5,4,5,6],[5,7,5,8],[6,4,6,6],[6,7,6,8],[7,4,7,6],[7,7,7,8],[10,4,10,6],[10,7,10,8],[35,5,35,7],[12,4,12,8],[32,4,32,8],[22,4,22,9],[28,5,28,7]]}
[]
[null,null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,null,{"$f":"Communication!E3","v":"Project Benefit-Cost Analysis - Communication Infrastructure"}]
[null,null,null,null,"Design-build and integrate fiber optic links between existing fiber infrastructure and signal systems, or procure and install cellular Ethernet modems. "]
[]
[null,null,null,null,"Region:",null,null,{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,null,"Proposed Project Name:",null,null,{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,null,"Requested By:",null,null,{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[]
[null,null,null,1,"What is the anticipated cost of the project?",null,null,{"$f":"Communication!I12","v":0}]
[]
[null,null,null,2,"Using each of the following Needs Analysis Tool presets, provide the anticipated level of need in the vicinity of the proposed project:"]
[null,null,null,null,null,null,null,"Default TIP",{"$f":"IF('Step 1 - Project Information'!F11=0,\"-\",'Step 1 - Project Information'!F11)","v":"-"}]
[null,null,null,null,null,null,null,"Safety",{"$f":"IF('Step 1 - Project Information'!F12=0,\"-\",'Step 1 - Project Information'!F12)","v":"-"}]
[null,null,null,null,null,null,null,"Mobility (Present)",{"$f":"IF('Step 1 - Project Information'!F13=0,\"-\",'Step 1 - Project Information'!F13)","v":"-"}]
[null,null,null,null,null,null,null,"Mobility (Future)",{"$f":"IF('Step 1 - Project Information'!F14=0,\"-\",'Step 1 - Project Information'!F14)","v":"-"}]
[null,null,null,null,null,null,null,"Service",{"$f":"IF('Step 1 - Project Information'!F15=0,\"-\",'Step 1 - Project Information'!F15)","v":"-"}]
[null,null,null,null,null,null,null,"Freight Performance",{"$f":"IF('Step 1 - Project Information'!F16=0,\"-\",'Step 1 - Project Information'!F16)","v":"-"}]
[]
[null,null,"Productivity Benefits"]
[]
[null,null,null,"P1. ","Will construction of this infrastructure eliminate the need to use currently leased fibers?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I25","v":0}]
[]
[null,null,null,null,"If so, how many fiber pairs are currently leased?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I28","v":0},"fiber pairs"]
[]
[null,null,null,null,null,"Annual cost of leasing fiber per mile",null,null,{"$f":"PARAMETERS!E43","v":600},"per mile"]
[]
[null,null,null,null,null,null,null,"Annual benefit of not leasing fiber:",{"$f":"IF(I24=\"Yes\",I29*I42*I27, 0)","v":0}]
[]
[null,null,null,"P2. ","How many device locations will be provided communication through this infrastructure?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I31","v":0},"locations"]
[]
[null,null,null,null,null,"Capital cost of cellular modem.\nAssume 5-year replacement",null,null,{"$f":"PARAMETERS!E40","v":2500}]
[null,null,null,null,null,"Annual operations cost of cellular modem",null,null,{"$f":"PARAMETERS!E41*12","v":1800}]
[]
[null,null,null,null,null,null,null,"Annual benefit of not using cell modems:",{"$f":"IF(I24=\"No\",I34*(I36/5)+I37*I34, 0)","v":0}]
[]
[null,null,null,"P3. ","How many miles of fiber will be constructed with this project?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I34","v":0},"miles"]
[]
[null,null,null,"P4. ","Will this communication infrastructure be leased as a revenue source?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I37","v":0}]
[]
[null,null,null,"P5. ","If leased, how many fiber pairs are expected to be leased?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I40","v":0},"fiber pairs"]
[]
[null,null,null,null,"Annual average revenue per mile fiber pair",null,null,null,{"$f":"PARAMETERS!E42","v":300},"per pair per mile"]
[]
[null,null,null,null,null,null,null,"Annual benefit of revenue from leasing fiber:",{"$f":"IF(I45=\"Yes\",I42*I48*I50, 0)","v":0}]
[]
[null,null,null,null,null,null,null,null,"Estimated Annual Benefit (Y1):",{"$f":"I31+I39+I52","v":0}]
[]
[]
[null,null,"Redundancy Benefits"]
[]
[null,null,null,"R1.","Will this installation of communication infrastructure provide a redundant link?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I48","v":0}]
[]
[null,null,null,"R2.","Does this link provide redundancy to communication infrastructure that is leased to others?"]
[null,null,null,null,"*assumes that an additional 10 hours of time is required by CE if leased to others for coordination."]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I51","v":0}]
[]
[null,null,null,"R3.","Based on the portion of the network that is provided redundancy with this link, how would you rate the importance of this link?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I54","v":0}]
[]
[null,null,null,"R4.","Approximately how many outages are experienced per year that will benefit from this redundant link?"]
[null,null,null,null,null,null,null,null,{"$f":"Communication!I57","v":0},"outages per year"]
[]
[null,null,null,null,null,null,null,"WisDOT average wage by occupation type (8.1 and Mark Lloyd email)."]
[null,null,null,null,null,null,null,null,{"$f":"PARAMETERS!E48","v":29.219711538461539},"IT hourly wage"]
[null,null,null,null,null,null,null,null,{"$f":"PARAMETERS!E49","v":29.219711538461539},"Electrical Tech hourly wage"]
[null,null,null,null,null,null,null,null,{"$f":"PARAMETERS!E50","v":40.560096153846153},"Civil Engineer hourly wage"]
[]
[null,null,null,null,null,null,null,"Estimated cost of an outage"]
[null,null,null,null,null,null,null,"Maintenance Ticket will be required regardless=",0]
[null,null,null,null,null,null,null,"Emergency Surcharge (Tapco email)=",{"$f":"PARAMETERS!E51","v":100}]
[null,null,null,null,null,null,null,"Cost of IT Tech handling emergency outage=",{"$f":"15*I73","v":438.29567307692309}]
[null,null,null,null,null,"*assumes 15 hours of time. Assumes that time would be absorbed during normal working hours if the outage had been prevented with a redundant link."]
[null,null,null,null,null,null,null,"Cost of Electrical Tech handling emergency outage=",{"$f":"15*I74","v":438.29567307692309}]
[null,null,null,null,null,"*assumes 15 hours of time. Assumes that time would be absorbed during normal working hours if the outage had been prevented with a redundant link."]
[null,null,null,null,null,null,null,"Cost of Engineer handling emergency outage=",{"$f":"IF(I67=\"Critical\",I75*45,IF(I67=\"Somewhat Critical\",I75*30,15*I75))+IF(I64=\"Yes\",I75*15,0)","v":608.40144230769226}]
[null,null,null,null,null,"*assumes 15 hours of time for three people if critical; 15 hours of two persons if somewhat critical; and 15 hours if important. Assumes an additional 15 hours of coordination time if the link also has a leasee. Assumes that time would be absorbed during normal working hours if the outage had been prevented with a redundant link."]
[null,null,null,null,null,null,null,"Total per outage=",{"$f":"SUM(I78:I84)","v":1584.9927884615386}]
[null,null,null,null,null,null,null,"Total per year=",{"$f":"I86*I70","v":0}]
[]
[null,null,null,null,null,null,null,null,"Estimated Annual Redundancy Benefit (Y1):",{"$f":"IF(I60=\"Yes\",I87,0)","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,null,"Y1","Annual Benefit",{"$f":"J54","v":0},null,"Y11","Annual Benefit",{"$f":"F121/(1.07^1)","v":0}]
[null,null,null,"Y1","Estimated Cost",{"$f":"H11","v":0},null,"Y11","Estimated Cost",{"$f":"F122/(1.07^1)","v":0}]
[]
[null,null,null,"Y2","PV Annual Benefit",{"$f":"F94/(1.07^1)","v":0},null,"Y12","PV Annual Benefit",{"$f":"J94/(1.07^1)","v":0}]
[null,null,null,"Y2","PV Estimated Cost",{"$f":"PARAMETERS!E46*I42/(1.07^1)","v":0},null,"Y12","PV Estimated Cost",{"$f":"J95/(1.07^1)","v":0}]
[]
[null,null,null,"Y3","PV Annual Benefit",{"$f":"F97/(1.07^1)","v":0},null,"Y13","PV Annual Benefit",{"$f":"J97/(1.07^1)","v":0}]
[null,null,null,"Y3","PV Estimated Cost",{"$f":"F98/(1.07^2)","v":0},null,"Y13","PV Estimated Cost",{"$f":"J98/(1.07^1)","v":0}]
[]
[null,null,null,"Y4","PV Annual Benefit",{"$f":"F100/(1.07^1)","v":0},null,"Y14","PV Annual Benefit",{"$f":"J100/(1.07^1)","v":0}]
[null,null,null,"Y4","PV Estimated Cost",{"$f":"F98/(1.07^3)","v":0},null,"Y14","PV Estimated Cost",{"$f":"J101/(1.07^1)","v":0}]
[]
[null,null,null,"Y5","PV Annual Benefit",{"$f":"F103/(1.07^1)","v":0},null,"Y15","PV Annual Benefit",{"$f":"J103/(1.07^1)","v":0}]
[null,null,null,"Y5","PV Estimated Cost",{"$f":"F104/(1.07^1)","v":0},null,"Y15","PV Estimated Cost",{"$f":"J104/(1.07^1)","v":0}]
[]
[null,null,null,"Y6","PV Annual Benefit",{"$f":"F106/(1.07^1)","v":0},null,"Y16","PV Annual Benefit",{"$f":"J106/(1.07^1)","v":0}]
[null,null,null,"Y6","PV Estimated Cost",{"$f":"F107/(1.07^1)","v":0},null,"Y16","PV Estimated Cost",{"$f":"J107/(1.07^1)","v":0}]
[]
[null,null,null,"Y7","PV Annual Benefit",{"$f":"F109/(1.07^1)","v":0},null,"Y17","PV Annual Benefit",{"$f":"J109/(1.07^1)","v":0}]
[null,null,null,"Y7","PV Estimated Cost",{"$f":"F110/(1.07^1)","v":0},null,"Y17","PV Estimated Cost",{"$f":"J110/(1.07^1)","v":0}]
[]
[null,null,null,"Y8","PV Annual Benefit",{"$f":"F112/(1.07^1)","v":0},null,"Y18","PV Annual Benefit",{"$f":"J112/(1.07^1)","v":0}]
[null,null,null,"Y8","PV Estimated Cost",{"$f":"F113/(1.07^1)","v":0},null,"Y18","PV Estimated Cost",{"$f":"J113/(1.07^1)","v":0}]
[]
[null,null,null,"Y9","PV Annual Benefit",{"$f":"F115/(1.07^1)","v":0},null,"Y19","PV Annual Benefit",{"$f":"J115/(1.07^1)","v":0}]
[null,null,null,"Y9","PV Estimated Cost",{"$f":"F116/(1.07^1)","v":0},null,"Y19","PV Estimated Cost",{"$f":"J116/(1.07^1)","v":0}]
[]
[null,null,null,"Y10","PV Annual Benefit",{"$f":"F118/(1.07^1)","v":0},null,"Y20","PV Annual Benefit",{"$f":"J118/(1.07^1)","v":0}]
[null,null,null,"Y10","PV Estimated Cost",{"$f":"F119/(1.07^1)","v":0},null,"Y20","PV Estimated Cost",{"$f":"J119/(1.07^1)","v":0}]
[]
[]
[null,null,null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"F94+F97+F100+F103+F106+F109+F112+F115+F118+F121+J94+J97+J100+J103+J106+J109+J112+J115+J118+J121","v":0}]
[null,null,null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"F95+F98+F101+F104+F107+F110+F113+F116+F119+F122+J95+J98+J101+J104+J107+J110+J113+J116+J119+J122","v":0}]
[]
[null,null,null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"I125/I126","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,null,null,"Return-on-Investment =",{"$f":"(I125-I126)/I126","v":{"$error":"#DIV/0!"}}]
[]
[null,null,null,null,null,null,null,null,"Back to Links Tab"]

{"sheet":"DMS","index":6,"n_rows":137,"n_cols":10,"rows_kept":137,"cut":false,"hidden":true,"merged":[[136,7,136,8],[67,4,67,8],[72,4,72,7],[61,6,61,8],[86,4,86,7],[80,4,80,7],[1,3,2,3],[1,4,1,8],[15,6,15,7],[3,4,3,6],[5,6,5,7],[6,6,6,7],[7,6,7,7],[14,4,14,7],[2,4,2,9],[10,4,10,6],[12,4,12,6],[16,6,16,7],[62,3,62,8],[26,4,26,8],[25,3,25,8],[40,4,40,8],[18,6,18,7],[32,4,32,8],[28,4,28,8],[41,4,41,8],[42,4,42,8],[39,3,39,8],[21,2,21,10],[33,3,33,8],[48,3,48,8],[31,4,31,8],[29,4,29,8],[43,4,43,8],[44,4,44,8],[45,4,45,8],[17,6,17,7],[27,4,27,8],[30,4,30,8],[56,4,56,8],[83,4,83,7],[89,4,89,7],[63,4,63,8],[64,4,64,8],[65,4,65,8],[66,4,66,8],[57,3,57,8],[68,3,68,8],[54,4,54,8],[46,4,46,8],[47,4,47,8],[55,4,55,8],[53,3,53,8]]}
[]
[null,null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,null,"Project Benefit-Cost Analysis - Dynamic Message Sign (DMS)"]
[]
[]
[null,null,null,null,null,"Region:",{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,null,null,"Proposed Project Name:",{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,null,null,"Requested By:",{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[]
[null,null,null,1,"What is the anticipated cost of the project (total design, construction, and communication cost)?"]
[]
[null,null,null,2,"What is the anticipated number of DMS to be installed for the project?"]
[]
[null,null,null,3,"Please complete the Guidance Analysis below to define the intent of the project. The summary of your results is listed here: "]
[null,null,null,null,null,"G1, Weather Conditions",{"$f":"J34","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G2, Traffic Conditions",{"$f":"J49","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G3, Traffic Control",{"$f":"J58","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G4, Special Events",{"$f":"J69","v":"NOT SUPPORTED"}]
[]
[null,null,"DMS Guidance Analysis:"]
[null,null,"The ITS Planning Guidance below was completed by an ENTERPRISE Pooled Fund Study and is used here to help define the intent of the DMS. "]
[]
[null,null,null,"DMS Guidance #1 - To Inform Travelers of Weather Conditions"]
[]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"If the location is prone to weather situations that travelers would not otherwise be forewarned about (e.g. spots where fog regularly forms, bridges that ice early, mountain passes with weather that differs from approaches)."]
[null,null,null,2,"If there is available road weather information for the area downstream of the candidate DMS location."]
[null,null,null,3,"If there is the capability (either manually by staff members or automated through a condition reporting system) to create event specific descriptions of weather conditions to be displayed on the DMS."]
[null,null,null,4,"If there is a need to disseminate event specific descriptions (rather than simply activating a flashing warning sign that says \"Weather Alert When Flashing\")."]
[null,null,null,5,"If there are options for either alternate routes or services, that might be described on the DMS, where travelers may wait out conditions."]
[null,null,null,6,"If flashing beacon signs have been tried and not proven to generate responses from travelers."]
[null,null,null,7,"If weather events contribute to a significant number of crashes or road closures which have major impacts to travelers."]
[null,null,null,"DMS Guidance #1 is:",null,null,null,null,null,{"$f":"IF(OR(J27=\"YES\",J31=\"YES\",J32=\"YES\",J33=\"YES\"),\"SUPPORTED\", IF(OR(J28=\"YES\",J29=\"YES\",J30=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\"))","v":"NOT SUPPORTED"}]
[]
[]
[]
[null,null,null,"DMS Guidance #2 - To Inform Travelers of Traffic Conditions"]
[]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"If the target area is monitored by CCTV cameras, traffic detectors, or another method of monitoring the conditions, or has travel times for the downstream stretch of road."]
[null,null,null,2,"Events occurring in the area unexpectedly impact or impede traffic (e.g. close a lane, encounter slow traffic in one or more lanes, or events on the shoulder) an average of at least two times per month."]
[null,null,null,3,"If there are acceptable alternate routes with capacity to accept vehicles that may deviate based upon the information."]
[null,null,null,4,"If the location is a stretch of road where no alternate route are possible and travelers would benefit from information describing the cause and/or extent of delays in order to relieve driver anxiety or confusion."]
[null,null,null,5,"If there are horizontal or vertical curves that create safety issues when traffic is stopped unexpectedly."]
[null,null,null,6,"The route being considered for the DMS has on average at least 2 hours per day of peak period travel where traffic flow exceeds 1,100 veh/hr/lane."]
[null,null,null,7,"The route being considered for the DMS has on average experienced conditions considered Level of Service C."]
[null,null,null,8,"The route being considered for the DMS experiences average annual daily traffic (AADT) of 16,800 for a 2 lane road; 33,600 for a 4 lane road; 50,400 for a 6 lane road, 67,200 for an 8 lane road."]
[null,null,null,"DMS Guidance #2 is:",null,null,null,null,null,{"$f":"IF(OR(J45=\"YES\",J46=\"YES\"),\"SUPPORTED\", IF(OR(J41=\"YES\",J42=\"YES\",J43=\"YES\",J44=\"YES\",J47=\"YES\",J48=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\"))","v":"NOT SUPPORTED"}]
[]
[]
[]
[null,null,null,"DMS Guidance #3 - Changing Traffic Control or Conditions (Work Zone)"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The candidate location is upstream of an area with construction or maintenance activities that are expected to cause at least 15 minutes of delay to the mainline traffic."]
[null,null,null,2,"If the candidate location is upstream of traffic control or construction/maintenance activities that are expected to change more frequently than once every 60 days."]
[null,null,null,3,"If the posted work zone speed limit is greater than 45 MPH."]
[null,null,null,"DMS Guidance #3 is:",null,null,null,null,null,{"$f":"IF(J55=\"YES\",\"SUPPORTED\", IF(AND(J56=\"YES\",J57=\"YES\"), \"SUPPORTED\",IF(OR(J55=\"YES\",J56=\"YES\",J57=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\")))","v":"NOT SUPPORTED"}]
[]
[]
[]
[null,null,null,"DMS Guidance #4 - Special Events"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"If the location contains a venue that houses ticketed events (typically with rapid and tight arrival patterns for a specified start time)."]
[null,null,null,2,"If the event venue typically houses at least two weekday (M-F) ticketed event per week (including seasonal sporting events that only occur during the season)."]
[null,null,null,3,"If the event venue typically houses at least 10 events per year attracting 30,000 visitors or more."]
[null,null,null,4,"If the setting of the venue is such that mainline traffic (not attending the event) is impacted by the conditions."]
[null,null,null,5,"If there are alternate parking or traffic options that could be displayed on signs to direct visitors to more preferred options."]
[null,null,null,"DMS Guidance #4 is:",null,null,null,null,null,{"$f":"IF(J66=\"YES\", \"SUPPORTED\", IF(AND(J64=\"YES\",J65=\"YES\",J66=\"YES\",J67=\"YES\",J68=\"YES\"),\"SUPPORTED\", IF(OR(J64=\"YES\",J65=\"YES\",J66=\"YES\",J67=\"YES\",J68=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\")))","v":"NOT SUPPORTED"}]
[]
[]
[null,null,null,2,2]
[null,null,null,4,"How many crashes, by severity, occurred in the past year at this/these corridor(s)? Use 1 miles upstream traffic from DMS sign. If using the Needs Analysis Tool to obtain crash data, divide the 5 year crashes by 5 to provide an average annual number of crashes."]
[null,null,null,null,null,null,null,"Fatal Injury"]
[null,null,null,null,null,null,null,"Suspected Serious Injury"]
[null,null,null,null,null,null,null,"Suspected Minor Injury"]
[null,null,null,null,null,null,null,"Possible Injury"]
[null,null,null,null,null,null,null,"Property Damage-Only"]
[]
[]
[null,null,null,5,"Estimate the average number of traffic/weather/special events that occur per year that will be positively affected by use of the proposed DMS."]
[null,null,null,null,null,null,null,null,"events per year"]
[]
[null,null,null,6,"Estimate the average duration (minutes) of traffic events (due to weather or incidents) that occur and will be positively affected by use of the proposed DMS."]
[null,null,null,null,null,null,null,null,"minutes"]
[]
[null,null,null,7,"Estimate the average travel time savings from adjusting one's route based on direction given on the proposed DMS."]
[null,null,null,null,null,null,null,null,"minutes"]
[]
[null,null,null,8,"Provide the current directional AADT along the corridor where the proposed DMS will be deployed (the Needs Analysis Tool may be used to obtain the value - note that volume is reported per roadway and not bi-directional)."]
[null,null,null,null,null,null,null,null,"veh per day"]
[]
[null,null,null,null,null,null,"Estimated Annual Safety Benefit:",{"$f":"'DMS Calc'!F75","v":0}]
[null,null,null,null,null,null,"Estimated Annual Mobility Benefit:",{"$f":"'DMS Calc'!F98","v":0}]
[null,null,null,null,null,null,"Estimated Annual Energy and Environment Benefit:",{"$f":"'DMS Calc'!F123","v":0}]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,null,"Y1","Annual Benefit",{"$f":"'DMS Calc'!E128","v":0},"Y11","Annual Benefit",{"$f":"'DMS Calc'!H128","v":0}]
[null,null,null,"Y1","Estimated Cost",{"$f":"'DMS Calc'!E129","v":0},"Y11","Estimated Cost",{"$f":"'DMS Calc'!H129","v":0}]
[]
[null,null,null,"Y2","PV Annual Benefit",{"$f":"'DMS Calc'!E131","v":0},"Y12","PV Annual Benefit",{"$f":"'DMS Calc'!H131","v":0}]
[null,null,null,"Y2","PV Estimated Cost",{"$f":"'DMS Calc'!E132","v":0},"Y12","PV Estimated Cost",{"$f":"'DMS Calc'!H132","v":0}]
[]
[null,null,null,"Y3","PV Annual Benefit",{"$f":"'DMS Calc'!E134","v":0},"Y13","PV Annual Benefit",{"$f":"'DMS Calc'!H134","v":0}]
[null,null,null,"Y3","PV Estimated Cost",{"$f":"'DMS Calc'!E135","v":0},"Y13","PV Estimated Cost",{"$f":"'DMS Calc'!H135","v":0}]
[]
[null,null,null,"Y4","PV Annual Benefit",{"$f":"'DMS Calc'!E137","v":0},"Y14","PV Annual Benefit",{"$f":"'DMS Calc'!H137","v":0}]
[null,null,null,"Y4","PV Estimated Cost",{"$f":"'DMS Calc'!E138","v":0},"Y14","PV Estimated Cost",{"$f":"'DMS Calc'!H138","v":0}]
[]
[null,null,null,"Y5","PV Annual Benefit",{"$f":"'DMS Calc'!E140","v":0},"Y15","PV Annual Benefit",{"$f":"'DMS Calc'!H140","v":0}]
[null,null,null,"Y5","PV Estimated Cost",{"$f":"'DMS Calc'!E141","v":0},"Y15","PV Estimated Cost",{"$f":"'DMS Calc'!H141","v":0}]
[]
[null,null,null,"Y6","PV Annual Benefit",{"$f":"'DMS Calc'!E143","v":0},"Y16","PV Annual Benefit",{"$f":"'DMS Calc'!H143","v":0}]
[null,null,null,"Y6","PV Estimated Cost",{"$f":"'DMS Calc'!E144","v":0},"Y16","PV Estimated Cost",{"$f":"'DMS Calc'!H144","v":0}]
[]
[null,null,null,"Y7","PV Annual Benefit",{"$f":"'DMS Calc'!E146","v":0},"Y17","PV Annual Benefit",{"$f":"'DMS Calc'!H146","v":0}]
[null,null,null,"Y7","PV Estimated Cost",{"$f":"'DMS Calc'!E147","v":0},"Y17","PV Estimated Cost",{"$f":"'DMS Calc'!H147","v":0}]
[]
[null,null,null,"Y8","PV Annual Benefit",{"$f":"'DMS Calc'!E149","v":0},"Y18","PV Annual Benefit",{"$f":"'DMS Calc'!H149","v":0}]
[null,null,null,"Y8","PV Estimated Cost",{"$f":"'DMS Calc'!E150","v":0},"Y18","PV Estimated Cost",{"$f":"'DMS Calc'!H150","v":0}]
[]
[null,null,null,"Y9","PV Annual Benefit",{"$f":"'DMS Calc'!E152","v":0},"Y19","PV Annual Benefit",{"$f":"'DMS Calc'!H152","v":0}]
[null,null,null,"Y9","PV Estimated Cost",{"$f":"'DMS Calc'!E153","v":0},"Y19","PV Estimated Cost",{"$f":"'DMS Calc'!H153","v":0}]
[]
[null,null,null,"Y10","PV Annual Benefit",{"$f":"'DMS Calc'!E155","v":0},"Y20","PV Annual Benefit",{"$f":"'DMS Calc'!H155","v":0}]
[null,null,null,"Y10","PV Estimated Cost",{"$f":"'DMS Calc'!E156","v":0},"Y20","PV Estimated Cost",{"$f":"'DMS Calc'!H156","v":0}]
[]
[]
[]
[null,null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"'DMS Calc'!G158","v":0}]
[null,null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"'DMS Calc'!G159","v":0}]
[]
[null,null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"'DMS Calc'!G161","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,null,"Return-on-Investment =",{"$f":"'DMS Calc'!G162","v":{"$error":"#DIV/0!"}}]
[]
[null,null,null,null,null,null,null,"Back to Links Tab"]

{"sheet":"DMS Calc","index":7,"n_rows":294,"n_cols":16,"rows_kept":294,"cut":false,"hidden":true,"merged":[[0,3,0,6],[102,3,102,5],[16,5,16,6],[17,5,17,6],[18,5,18,6],[19,5,19,6],[66,3,66,5],[84,3,84,4],[79,3,79,6],[27,3,27,6],[82,3,82,4],[56,3,56,5],[92,3,92,5],[4,4,4,5],[5,4,5,5],[6,4,6,5],[9,5,9,6],[11,3,11,5],[35,5,35,6],[36,5,36,6],[37,5,37,6],[116,3,116,4],[158,6,158,7],[1,3,1,7],[42,3,42,6],[49,3,49,6],[59,3,59,6],[105,3,105,5],[157,6,157,7],[38,5,38,6],[34,3,34,5],[13,3,13,6],[14,5,14,6],[21,3,21,6],[24,3,24,6],[30,3,30,6],[15,5,15,6]]}
[null,null,null,{"$f":"DMS!E2","v":"Transportation System Management and Operations - Traffic Infrastructure Process"}]
[null,null,null,{"$f":"DMS!E3","v":"Project Benefit-Cost Analysis - Dynamic Message Sign (DMS)"}]
[null,null,null,"New DMS Deployment."]
[]
[null,null,null,"Region:",{"$f":"DMS!G6","v":"-"}]
[null,null,null,"Proposed Project Name:",{"$f":"DMS!G7","v":"-"}]
[null,null,null,"Requested By:",{"$f":"DMS!G8","v":"-"}]
[]
[]
[null,null,1,"What is the anticipated cost of the project?",null,{"$f":"DMS!H11","v":0}]
[]
[null,null,2,"What is the anticipated number of DMS to be installed for the project?",null,null,{"$f":"DMS!H13","v":0}]
[]
[null,null,3,"Provide the anticipated level of need in the vicinity of the proposed project using the following Needs Analysis Tool presets:"]
[null,null,null,null,"Default TIP",{"$f":"'Step 1 - Project Information'!F11","v":0}]
[null,null,null,null,"Safety",{"$f":"'Step 1 - Project Information'!F12","v":0}]
[null,null,null,null,"Mobility (Present)",{"$f":"'Step 1 - Project Information'!F13","v":0}]
[null,null,null,null,"Mobility (Future)",{"$f":"'Step 1 - Project Information'!F14","v":0}]
[null,null,null,null,"Service",{"$f":"'Step 1 - Project Information'!F15","v":0}]
[null,null,null,null,"Freight Performance",{"$f":"'Step 1 - Project Information'!F16","v":0}]
[]
[null,null,4,"Estimate the average number of traffic events that occur per year that will be positively effected by use of the proposed DMS."]
[null,null,null,null,null,{"$f":"DMS!H82","v":0},"events per year"]
[]
[null,null,5,"Estimate the average duration (minutes) of traffic events that occur and will be positively effected by use of the proposed DMS."]
[null,null,null,null,null,{"$f":"DMS!H85","v":0},"minutes"]
[]
[null,null,6,"Estimate the average travel time savings from adjusting one's route based on direction given on the proposed DMS."]
[null,null,null,null,null,{"$f":"DMS!H88","v":0},"minutes"]
[]
[null,null,7,"Provide the current directional AADT along the corridor where the proposed DMS will be deployed (the Needs Analysis Tool may be used to obtain the value)."]
[null,null,null,null,null,{"$f":"DMS!H91","v":0},"veh per day"]
[]
[]
[null,null,8,{"$f":"DMS!E15","v":"Please complete the Guidance Analysis below to define the intent of the project. The summary of your results is listed here: "}]
[null,null,null,null,{"$f":"DMS!F16","v":"G1, Weather Conditions"},{"$f":"DMS!G16","v":"NOT SUPPORTED"}]
[null,null,null,null,{"$f":"DMS!F17","v":"G2, Traffic Conditions"},{"$f":"DMS!G17","v":"NOT SUPPORTED"}]
[null,null,null,null,{"$f":"DMS!F18","v":"G3, Traffic Control"},{"$f":"DMS!G18","v":"NOT SUPPORTED"}]
[null,null,null,null,{"$f":"DMS!F19","v":"G4, Special Events"},{"$f":"DMS!G19","v":"NOT SUPPORTED"}]
[]
[null,null,"Safety Benefits "]
[]
[null,null,"S2. ","How many crashes, by type, occurred in the past year at this intersection?"]
[null,null,null,null,null,{"$f":"DMS!G74","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"DMS!G75","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"DMS!G76","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"DMS!G77","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"DMS!G78","v":0},"Property Damage-Only"]
[]
[null,null,"S2. ","Guidance on Treatment of the Economic Value of Statistical Life in the US (2019) estimates plus 1.6% (2020), 7.0% (2021) the consumer price index, as per the US Bureau of Labor Statistics in the MN region (1). "]
[null,null,null,null,null,{"$f":"PARAMETERS!E7","v":13000000},"Fatal Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E8","v":1235728},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E9","v":243152},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E10","v":116168},"Possible Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E11","v":5200},"Property Damage-Only"]
[]
[null,null,"S3. ","Average crash reduction due to DMS Deployment (14)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E14","v":8.6},"percent"]
[]
[null,null,"S4. ","Average theoretical number of crashes reduced at proposed intersection per severity type after DMS deployment:"]
[null,null,null,null,null,{"$f":"F44*($F$58/100)","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"F45*($F$58/100)","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"F46*($F$58/100)","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,0,"Possible Injury"]
[null,null,null,null,null,0,"Property Damage-Only"]
[]
[null,null,"S5. ","Expected safety benefit per crash severity:"]
[null,null,null,null,null,{"$f":"(F61)*F51","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"(F62)*F52","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"(F63)*F53","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,0,"Possible Injury"]
[null,null,null,null,null,0,"Property Damage-Only"]
[]
[]
[null,null,null,null,"Estimated Annual Safety Benefit (G1, G2, G3, G4):",{"$f":"IF(OR($F$36=\"SUPPORTED\",$F$37=\"SUPPORTED\",$F$38=\"SUPPORTED\",$F$39=\"SUPPORTED\"),ROUND((SUM(F68:F72)),-3),IF(OR($F$36=\"PARTIALLY SUPPORTED\",$F$37=\"PARTIALLY SUPPORTED\",$F$38=\"PARTIALLY SUPPORTED\",$F$39=\"PARTIALLY SUPPORTED\"),ROUND((SUM(F68:F72))*0.25,-3),0))","v":0}]
[]
[]
[null,null,"Mobility Benefits (G1, G2, G3, G4)"]
[]
[null,null,"M1.","Approximate percentage of travelers who adjusted their travel routes based on information provided by DMS (14).",null,null,null,null,null,"The WI study actually says 68 and 72 percent. However, I conservatively only used the percentage of people who reported adjusting their routes more than 5 times a month."]
[null,null,null,null,null,{"$f":"PARAMETERS!E27","v":18},"percent"]
[]
[null,null,"M2.","Average number of vehicles offered the opportunity to adjust route per event (15):",null,{"$f":"(F26/60)*F32*0.8/17","v":0},"vehicles",null,null,"*Assumes that 80% of traffic will occur in 17 hours of the day based on average traffic distribution provided in source."]
[]
[null,null,"M3.","Average number of travelers (vehicles) who adjust their routes per event:",null,{"$f":"F83*(F81/100)","v":0}]
[]
[null,null,"M4.","Average travel time savings per event:",null,{"$f":"F85*F29","v":0},"minutes"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"3*1.3*(15/1.3)","v":45.000000000000007}]
[null,null,"M5.","Average travel time savings per year:",null,{"$f":"(F87*F23)/60","v":0},"hours"]
[]
[null,null,"M6.","Average vehicle occupancy (6).",null,{"$f":"PARAMETERS!E23","v":1.67},"persons per vehicle",null,null,null,null,null,null,null,null,{"$f":"1-0.29-0.34-0.18","v":0.18999999999999995}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"1-0.33-0.32-0.24","v":0.10999999999999993}]
[null,null,"M7.","Estimated average person travel time savings per year. "]
[null,null,null,null,null,{"$f":"F89*F91","v":0},"hours per year"]
[]
[null,null,"M8.","Value of Travel Time Savings per FHWA (7).",null,{"$f":"PARAMETERS!E24","v":20.384000000000004},"per hour TTS"]
[]
[null,null,null,null,"Estimated Annual Mobility Benefit:",{"$f":"IF(OR($F$36=\"SUPPORTED\",$F$37=\"SUPPORTED\",$F$38=\"SUPPORTED\",$F$39=\"SUPPORTED\"),ROUND((F96)*F94,-3),IF(OR($F$36=\"PARTIALLY SUPPORTED\",$F$37=\"PARTIALLY SUPPORTED\",$F$38=\"PARTIALLY SUPPORTED\",$F$39=\"PARTIALLY SUPPORTED\"),ROUND((F96)*F94*0.25,-3),0))","v":0}]
[]
[]
[null,null,"Energy and Environment Benefits"]
[]
[null,null,"E1. ","Estimated average travel time savings per year per vehicle. Estimation assumes travel time savings is predominate during peak period weekday times."]
[null,null,null,null,null,{"$f":"IF(NOT(F98=0),F89,0)","v":0},"hours per year"]
[]
[null,null,"E2. ","Average fuel consumption per minute of idle time (1). It is assumed that the reduction in fuel consumption would be based primarily on the reduction of idle time. This is conservative because there would be fuel savings due to the smoother traffic progression."]
[null,null,null,null,null,{"$f":"PARAMETERS!E34","v":6.4999999999999997E-3},"gal / min"]
[]
[null,null,"E3. ","Average fuel consumption reduction per year.",null,{"$f":"F107*F104*60","v":0},"gallons / year"]
[]
[null,null,"E4. ","Average Wisconsin fuel cost (12).",null,{"$f":"PARAMETERS!E33","v":3.31}]
[]
[null,null,null,null,"Energy and Environment Benefit Subtotal (gasoline):",{"$f":"F111*F109","v":0}]
[]
[null,null,"E5. ","Average CO2 emitted per gallon of gasoline burned (6).",null,{"$f":"PARAMETERS!E35","v":8.9200000000000008E-3},"metric tons per gallon"]
[]
[null,null,"E6. ","Average CO2 emission reduction due to the proposed project.",null,{"$f":"F115*F109","v":0},"metric tons"]
[]
[null,null,"E7. ","Average cost per metric ton of CO2 (16).",null,{"$f":"PARAMETERS!E36","v":228},"$ per metric ton"]
[]
[null,null,null,null,"Energy and Environment Benefit Subtotal (CO2):",{"$f":"F119*F117","v":0}]
[]
[null,null,null,null,"Annual Energy and Environment Benefit:",{"$f":"IF(OR(F36=\"SUPPORTED\",F37=\"SUPPORTED\",F38=\"SUPPORTED\",F39=\"SUPPORTED\"),ROUND(F121+F113,-3),IF(OR(F36=\"PARTIALLY SUPPORTED\",F37=\"PARTIALLY SUPPORTED\",F38=\"PARTIALLY SUPPORTED\",F39=\"PARTIALLY SUPPORTED\"),ROUND((F121+F113)*0.25,-3),0))","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,"Y1","Annual Benefit",{"$f":"F123+F98+F75","v":0},"Y11","Annual Benefit",{"$f":"E155/(1.07^1)","v":0}]
[null,null,"Y1","Estimated Cost",{"$f":"DMS!H11","v":0},"Y11","Estimated Cost",{"$f":"E132/(1.07^10)","v":0}]
[]
[null,null,"Y2","PV Annual Benefit",{"$f":"E128/(1.07^1)","v":0},"Y12","PV Annual Benefit",{"$f":"H128/(1.07^1)","v":0}]
[null,null,"Y2","PV Estimated Cost",{"$f":"(PARAMETERS!E45*DMS!H13)/(1.07^1)","v":0},"Y12","PV Estimated Cost",{"$f":"E132/(1.07^11)","v":0}]
[]
[null,null,"Y3","PV Annual Benefit",{"$f":"E131/(1.07^1)","v":0},"Y13","PV Annual Benefit",{"$f":"H131/(1.07^1)","v":0}]
[null,null,"Y3","PV Estimated Cost",{"$f":"E132/(1.07^2)","v":0},"Y13","PV Estimated Cost",{"$f":"E132/(1.07^12)","v":0}]
[]
[null,null,"Y4","PV Annual Benefit",{"$f":"E134/(1.07^1)","v":0},"Y14","PV Annual Benefit",{"$f":"H134/(1.07^1)","v":0}]
[null,null,"Y4","PV Estimated Cost",{"$f":"E132/(1.07^3)","v":0},"Y14","PV Estimated Cost",{"$f":"E132/(1.07^13)","v":0}]
[]
[null,null,"Y5","PV Annual Benefit",{"$f":"E137/(1.07^1)","v":0},"Y15","PV Annual Benefit",{"$f":"H137/(1.07^1)","v":0}]
[null,null,"Y5","PV Estimated Cost",{"$f":"E132/(1.07^4)","v":0},"Y15","PV Estimated Cost",{"$f":"E132/(1.07^14)","v":0}]
[]
[null,null,"Y6","PV Annual Benefit",{"$f":"E140/(1.07^1)","v":0},"Y16","PV Annual Benefit",{"$f":"H140/(1.07^1)","v":0}]
[null,null,"Y6","PV Estimated Cost",{"$f":"E132/(1.07^5)","v":0},"Y16","PV Estimated Cost",{"$f":"E132/(1.07^15)","v":0}]
[]
[null,null,"Y7","PV Annual Benefit",{"$f":"E143/(1.07^1)","v":0},"Y17","PV Annual Benefit",{"$f":"H143/(1.07^1)","v":0}]
[null,null,"Y7","PV Estimated Cost",{"$f":"E132/(1.07^6)","v":0},"Y17","PV Estimated Cost",{"$f":"E132/(1.07^16)","v":0}]
[]
[null,null,"Y8","PV Annual Benefit",{"$f":"E146/(1.07^1)","v":0},"Y18","PV Annual Benefit",{"$f":"H146/(1.07^1)","v":0}]
[null,null,"Y8","PV Estimated Cost",{"$f":"E132/(1.07^7)","v":0},"Y18","PV Estimated Cost",{"$f":"E132/(1.07^17)","v":0}]
[null,null,null,7]
[null,null,"Y9","PV Annual Benefit",{"$f":"E149/(1.07^1)","v":0},"Y19","PV Annual Benefit",{"$f":"H149/(1.07^1)","v":0}]
[null,null,"Y9","PV Estimated Cost",{"$f":"E132/(1.07^8)","v":0},"Y19","PV Estimated Cost",{"$f":"E132/(1.07^18)","v":0}]
[]
[null,null,"Y10","PV Annual Benefit",{"$f":"E152/(1.07^1)","v":0},"Y20","PV Annual Benefit",{"$f":"H152/(1.07^1)","v":0}]
[null,null,"Y10","PV Estimated Cost",{"$f":"E132/(1.07^9)","v":0},"Y20","PV Estimated Cost",{"$f":"E132/(1.07^19)","v":0}]
[]
[null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"E128+E131+E134+E137+E140+E143+E146+E149+E152+H128+E148+H131+H134+H137+H140+H143+H146+H149+H152+E155+H155","v":0}]
[null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"E129+E132+E135+E138+E141+E144+E147+E150+E153+H129+E149+H132+H135+H138+H141+H144+H147+H150+H153+E156+H156","v":0}]
[]
[null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"G158/G159","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,"Return-on-Investment =",{"$f":"(G158-G159)/G159","v":{"$error":"#DIV/0!"}}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,7]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,7]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,7]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,7]

{"sheet":"CCTV","index":8,"n_rows":155,"n_cols":10,"rows_kept":155,"cut":false,"hidden":true,"merged":[[78,4,78,7],[81,6,81,7],[82,6,82,7],[73,4,73,8],[80,6,80,7],[75,3,75,8],[74,4,74,8],[79,6,79,7],[83,6,83,7],[84,6,84,7],[154,7,154,8],[88,4,88,7],[117,4,117,7],[119,4,119,7],[100,4,100,7],[106,4,106,7],[109,4,109,7],[103,4,103,7],[10,6,10,7],[13,6,13,7],[28,3,28,8],[61,3,61,8],[7,6,7,7],[15,4,15,8],[16,6,16,7],[17,6,17,7],[18,6,18,7],[19,6,19,7],[56,3,56,8],[57,4,57,8],[40,4,40,8],[41,4,41,8],[42,4,42,8],[24,2,24,10],[6,6,6,7],[1,3,2,3],[1,4,1,8],[3,4,3,6],[5,6,5,7],[2,4,2,9],[39,4,39,8],[32,3,32,8],[26,3,26,8],[29,4,29,8],[30,4,30,8],[31,4,31,8],[43,4,43,8],[72,4,72,8],[65,4,65,8],[66,4,66,8],[67,4,67,8],[52,4,52,8],[55,6,55,8],[53,3,53,8],[58,4,58,8],[63,6,63,8],[64,3,64,8],[68,3,68,8],[70,6,70,8],[71,3,71,8],[70,3,70,5],[12,4,12,7],[9,4,9,7],[44,3,44,8],[59,4,59,8],[60,4,60,8],[46,6,46,8],[47,3,47,8],[48,4,48,8],[49,4,49,8],[50,4,50,8],[51,4,51,8],[20,6,20,7],[21,6,21,7],[37,4,37,8],[36,3,36,8],[38,4,38,8]]}
[]
[null,null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,null,"Project Benefit-Cost Analysis - CCTV Camera"]
[]
[]
[null,null,null,null,null,"Region:",{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,null,null,"Proposed Project Name:",{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,null,null,"Requested By:",{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[null,null,null,1,"What is the anticipated cost of the project?"]
[]
[]
[null,null,null,2,"What is the anticipated number of cameras to be installed for the project?"]
[]
[]
[null,null,null,3,"Please complete the guidance analysis below. If more than one camera is being requested, it is recommended that each location is considered separately because there may be different responses to the questions below. However, if multiple camera locations are included in this analysis, respond to each question collectively. Based on your responses , the following CCTV Camera Guidance have been met: "]
[null,null,null,null,null,"G1, Signal Control",{"$f":"J33","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G2, Traffic Incident",{"$f":"J45","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G3, Weather Verification",{"$f":"J54","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G4, Traveler Information",{"$f":"J62","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G5, Field Device Verification",{"$f":"J69","v":"NOT SUPPORTED"}]
[null,null,null,null,null,"G6, Work Zone",{"$f":"J76","v":"NOT SUPPORTED"}]
[]
[null,null,"CCTV Camera Guidance Analysis:"]
[null,null,"The ITS Planning Guidance below was completed by an ENTERPRISE Pooled Fund Study and is used here to help define the intent of the proposed camera. "]
[]
[null,null,null,"CCTV Guidance #1 - Traffic Observation for Signal Control Changes"]
[]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"There are typically periods of time at least twice per week of ‘loaded’ cycles (i.e. where the vehicles in the queue do not all dissipate in one green cycle) that last 15 minutes or longer."]
[null,null,null,2,"The signalized intersection has sufficient cross street traffic such that visual observation is needed determining if alternate signal timings are appropriate to benefit the primary direction of flow (i.e. in order to verify that the secondary street is not backing up)."]
[null,null,null,3,"The operations personnel have the ability to activate special event timing plans remotely."]
[null,null,null,"CCTV Guidance #1 is:",null,null,null,null,null,{"$f":"IF(OR(J30=\"YES\",J31=\"YES\",J32=\"YES\"),\"SUPPORTED\", \"NOT SUPPORTED\")","v":"NOT SUPPORTED"}]
[]
[null,null,null,"CCTV Guidance #2 - Traffic Incident or Event Verification"]
[]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The candidate location encounters incidents as frequently as twice per month for arterial streets or once per month for freeways."]
[null,null,null,2,"The incidents and events that occur on freeways typically cause delay to travelers of at least 15 minutes while the incident is active and has not been cleared."]
[null,null,null,3,"The incidents and events that occur on arterials typically impact travel such that the signal progression is no longer occurring and vehicles in queues are unable to clear intersections during the cycle's allotted green time."]
[null,null,null,4,"Incident location verification is needed by 911 dispatchers (e.g. large complex interchange where drivers don't know where they are, closely spaced interchanges)."]
[null,null,null,5,"The location encounters at least 2 hours per day of peak period travel where traffic flow exceeds 1,100 veh/hr/lane."]
[null,null,null,6,"The location encounters conditions considered Level of Service C."]
[null,null,null,7,"The location encounters average annual daily traffic (AADT) of 16,800 for a 2 lane road; 33,600 for a 4 lane road; 50,400 for a 6 lane road, 67,200 for an 8 lane road."]
[null,null,null,"CCTV Guidance #2 is:",null,null,null,null,null,{"$f":"IF(OR(J38=\"YES\",J39=\"YES\",J40=\"YES\",J41=\"YES\"),\"SUPPORTED\",IF(AND(J42=\"YES\",J43=\"YES\",J44=\"YES\"), \"SUPPORTED\", IF(OR(J42=\"YES\",J43=\"YES\",J44=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\")))","v":"NOT SUPPORTED"}]
[]
[null,null,null,"CCTV Guidance #3 - Weather Verification"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The location typically encounters at least 10 weather events each season."]
[null,null,null,2,"Weather events have a significant impact to travelers at this location (due to such circumstances as either: local terrain, lack of alternate routes, winding or steep routes), and it is a location that travelers are frequently concerned about."]
[null,null,null,3,"If there are no nearby weather sensors reporting real-time conditions."]
[null,null,null,4,"If there are no regular manual observations and reports of visibility, precipitation, or pavement temperatures."]
[null,null,null,5,"If nearby weather sensors would be enhanced through the capability of visual observation."]
[null,null,null,"CCTV Guidance #3 is:",null,null,null,null,null,{"$f":"IF(OR(J49=\"YES\",J50=\"YES\"),\"SUPPORTED\",IF(OR(J51=\"YES\",J52=\"YES\",J53=\"YES\"),\"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\"))","v":"NOT SUPPORTED"}]
[]
[null,null,null,"CCTV Guidance #4 - Traveler Information"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The location visible by the camera image has a history of congestion on a regular basis (i.e. each commuter day is a candidate for congestion)."]
[null,null,null,2,"The location is prone to weather situations that travelers would not otherwise be forewarned about (e.g. spots where fog regularly forms, bridges that ice early, mountain passes with weather that differs from approaches)."]
[null,null,null,3,"The location is in a remote area that receives considerable traffic volume due to commercial vehicle traffic or recreational traffic."]
[null,null,null,4,"The majority of travelers to the area have Internet access in proximity to the area where camera images are of value to travelers prior to departure."]
[null,null,null,"CCTV Guidance #4 is:",null,null,null,null,null,{"$f":"IF(OR(J58=\"YES\",J59=\"YES\"),\"SUPPORTED\", IF(OR(J61=\"YES\",J60=\"YES\"), \"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\"))","v":"NOT SUPPORTED"}]
[]
[null,null,null,"CCTV Guidance #5 - Field Device Verification"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The proper operations of the device can be remotely monitored by a camera."]
[null,null,null,2,"The failure of the device presents a safety hazard."]
[null,null,null,3,"The camera operation would avoid unnecessary trips to verify functionality of the field device."]
[null,null,null,"CCTV Guidance #5 is:",null,null,null,null,null,{"$f":"IF(OR(J66=\"YES\",J67=\"YES\",J68=\"YES\"),\"SUPPORTED\",\"NOT SUPPORTED\")","v":"NOT SUPPORTED"}]
[]
[null,null,null,"CCTV Guidance #6 - Intelligent Work Zone"]
[null,null,null,"Consideration",null,null,null,null,null,"Response"]
[null,null,null,1,"The alignment or traffic control that is visible by a camera image is expected to change periodically during the construction period."]
[null,null,null,2,"The construction zone encounters periods of queues or delays for at least 30 minutes each day."]
[null,null,null,3,"The construction zone is in a location where there is not a convenient alternate route for the majority of traffic to deviate from the typical route."]
[null,null,null,"CCTV Guidance #6 is:",null,null,null,null,null,{"$f":"IF(AND(J73=\"YES\",J75=\"YES\"),\"SUPPORTED\",IF(J74=\"YES\",\"SUPPORTED\",IF(OR(J73=\"YES\",J75=\"YES\"),\"PARTIALLY SUPPORTED\",\"NOT SUPPORTED\")))","v":"NOT SUPPORTED"}]
[]
[]
[null,null,null,4,"Using each of the following Needs Analysis Tool presets, provide the anticipated level of need in the vicinity of the proposed project (Project Information sheet will populate these cells):"]
[null,null,null,null,null,"Default TIP",{"$f":"IF('Step 1 - Project Information'!F11=0,\"-\",'Step 1 - Project Information'!F11)","v":"-"}]
[null,null,null,null,null,"Safety",{"$f":"IF('Step 1 - Project Information'!F12=0,\"-\",'Step 1 - Project Information'!F12)","v":"-"}]
[null,null,null,null,null,"Mobility (Present)",{"$f":"IF('Step 1 - Project Information'!F13=0,\"-\",'Step 1 - Project Information'!F13)","v":"-"}]
[null,null,null,null,null,"Mobility (Future)",{"$f":"IF('Step 1 - Project Information'!F14=0,\"-\",'Step 1 - Project Information'!F14)","v":"-"}]
[null,null,null,null,null,"Service",{"$f":"IF('Step 1 - Project Information'!F15=0,\"-\",'Step 1 - Project Information'!F15)","v":"-"}]
[null,null,null,null,null,"Freight Performance",{"$f":"IF('Step 1 - Project Information'!F16=0,\"-\",'Step 1 - Project Information'!F16)","v":"-"}]
[]
[null,null,"Safety Benefits"]
[]
[null,null,null,"S1. ","How many crashes, by severity type, occurred in the past year at this intersection or corridor? If using the Needs Analysis Tool to obtain crash data, divide the 5 year crashes by 5 to provide an average annual number of crashes."]
[null,null,null,null,null,null,null,null,"Fatal Injury"]
[null,null,null,null,null,null,null,null,"Suspected Serious Injury"]
[null,null,null,null,null,null,null,null,"Suspected Minor Injury"]
[null,null,null,null,null,null,null,null,"Possible Injury"]
[null,null,null,null,null,null,null,null,"Property Damage-Only"]
[]
[null,null,null,null,null,null,"Estimated Safety Benefit:",{"$f":"'CCTV Calc'!F75+'CCTV Calc'!F76","v":0}]
[]
[]
[null,null,"Mobility Benefits"]
[]
[null,null,null,"M1 (G1,G2, G3, G4, G6). ","What is the estimated AADT for all vehicles entering the intersection (the Needs Analysis Tool may be used to obtain the value - note that volume is reported per roadway and not bi-directional)?"]
[null,null,null,null,null,null,null,null,"vehicles per day"]
[]
[null,null,null,"M2 (G1).","What is the average Relative Need at this intersection according to the Needs Analysis Tool - Service preset?"]
[]
[]
[null,null,null,"M1 (G2, G3, G4, G6).","Estimate the average number of traffic events that occur per year within site distance of the proposed camera(s)."]
[null,null,null,null,null,null,null,null,"events per year"]
[]
[null,null,null,"M2 (G2, G3, G4, G6).","Estimate the average duration (minutes) of the traffic events that occur within site distance of the proposed camera."]
[null,null,null,null,null,null,null,null,"minutes"]
[]
[null,null,null,null,null,null,"Estimated Annual Mobility Benefit:",{"$f":"'CCTV Calc'!F135+'CCTV Calc'!F136","v":0}]
[]
[]
[null,null,"Productivity Benefits"]
[]
[null,null,null,"P1. ","It is assumed that productivity benefits will be realized through reduced maintenance efforts / verification site visits (i.e. if a camera can be used to verify the location of debris or verify functionality of traffic signal). Estimate for how long maintenance efforts / verification site visits have been increasing at the proposed device replacement location(s). "]
[]
[null,null,null,"P2. ","How many TOAMS tickets have been required at this location over the length of time indicated above in P1? (if request is for multiple intersections, include cumulative total here)."]
[null,null,null,null,null,null,null,null,"tickets"]
[null,null,null,"P3. ","What was the total cost of these tickets?"]
[]
[]
[null,null,null,"P4. ","What is the anticipated percent reduction of maintenance tickets due to the proposed project?"]
[]
[]
[null,null,null,null,null,null,"Estimated Annual Productivity Benefit:",{"$f":"'CCTV Calc'!F154","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,null,"Y1","Annual Benefit",{"$f":"'CCTV Calc'!E186","v":0},"Y6","PV Annual Benefit",{"$f":"'CCTV Calc'!H186","v":0}]
[null,null,null,"Y1","Estimated Cost",{"$f":"'CCTV Calc'!E187","v":0},"Y6","PV Estimated Cost",{"$f":"'CCTV Calc'!H187","v":0}]
[]
[null,null,null,"Y2","PV Annual Benefit",{"$f":"'CCTV Calc'!E189","v":0},"Y7","PV Annual Benefit",{"$f":"'CCTV Calc'!H189","v":0}]
[null,null,null,"Y2","PV Estimated Cost",{"$f":"'CCTV Calc'!E190","v":0},"Y7","PV Estimated Cost",{"$f":"'CCTV Calc'!H190","v":0}]
[]
[null,null,null,"Y3","PV Annual Benefit",{"$f":"'CCTV Calc'!E192","v":0},"Y8","PV Annual Benefit",{"$f":"'CCTV Calc'!H192","v":0}]
[null,null,null,"Y3","PV Estimated Cost",{"$f":"'CCTV Calc'!E193","v":0},"Y8","PV Estimated Cost",{"$f":"'CCTV Calc'!H193","v":0}]
[]
[null,null,null,"Y4","PV Annual Benefit",{"$f":"'CCTV Calc'!E195","v":0},"Y9","PV Annual Benefit",{"$f":"'CCTV Calc'!H195","v":0}]
[null,null,null,"Y4","PV Estimated Cost",{"$f":"'CCTV Calc'!E196","v":0},"Y9","PV Estimated Cost",{"$f":"'CCTV Calc'!H196","v":0}]
[]
[null,null,null,"Y5","PV Annual Benefit",{"$f":"'CCTV Calc'!E198","v":0},"Y10","PV Annual Benefit",{"$f":"'CCTV Calc'!H198","v":0}]
[null,null,null,"Y5","PV Estimated Cost",{"$f":"'CCTV Calc'!E199","v":0},"Y10","PV Estimated Cost",{"$f":"'CCTV Calc'!H199","v":0}]
[]
[]
[null,null,null,null,null,null,"10-Year Monetized Benefits =",{"$f":"'CCTV Calc'!G201","v":0}]
[null,null,null,null,null,null,"10-Year Estimated Costs =",{"$f":"'CCTV Calc'!G202","v":0}]
[]
[null,null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"'CCTV Calc'!G204","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,null,"Return-on-Investment =",{"$f":"'CCTV Calc'!G205","v":{"$error":"#DIV/0!"}}]
[]
[null,null,null,null,null,null,null,"Back to Links Tab"]

{"sheet":"CCTV Calc","index":9,"n_rows":205,"n_cols":10,"rows_kept":205,"cut":false,"hidden":true,"merged":[[172,3,172,4],[168,3,168,4],[166,3,166,4],[174,3,174,4],[1,3,1,7],[13,5,13,6],[6,5,6,6],[9,3,9,4],[11,3,11,6],[12,5,12,6],[10,5,10,6],[31,3,31,5],[20,3,20,6],[21,5,21,6],[22,5,22,6],[23,5,23,6],[0,2,1,2],[0,3,0,7],[2,3,2,5],[4,5,4,6],[5,5,5,6],[24,5,24,6],[25,5,25,6],[26,5,26,6],[109,3,109,6],[38,3,38,5],[45,3,45,5],[48,3,48,5],[50,3,50,5],[53,3,53,5],[60,3,60,5],[67,3,67,5],[80,3,80,5],[83,3,83,6],[106,3,106,6],[115,3,115,6],[118,3,118,6],[201,6,201,7],[99,3,99,5],[14,5,14,6],[15,5,15,6],[16,5,16,6],[17,5,17,6],[200,6,200,7],[149,3,149,5],[158,3,158,5],[163,3,163,5],[112,3,112,6],[121,3,121,6],[140,3,140,5],[144,3,144,5]]}
[null,null,null,"Transportation System Management and Operations"]
[null,null,null,"Project Benefit-Cost Analysis - CCTV Camera"]
[]
[]
[null,null,null,null,"Region:",{"$f":"'Step 1 - Project Information'!E5","v":0}]
[null,null,null,null,"Proposed Project Name:",{"$f":"'Step 1 - Project Information'!E6","v":0}]
[null,null,null,null,"Requested By:",{"$f":"'Step 1 - Project Information'!E7","v":0}]
[]
[]
[null,null,1,"What is the anticipated cost of the project?"]
[null,null,null,null,null,{"$f":"CCTV!G11","v":0}]
[null,null,2,"Provide the anticipated level of need in the vicinity of the proposed project using the following Needs Analysis Tool presets:"]
[null,null,null,null,"Default TIP",{"$f":"'Step 1 - Project Information'!F11","v":0}]
[null,null,null,null,"Safety",{"$f":"'Step 1 - Project Information'!F12","v":0}]
[null,null,null,null,"Mobility (Present)",{"$f":"'Step 1 - Project Information'!F13","v":0}]
[null,null,null,null,"Mobility (Future)",{"$f":"'Step 1 - Project Information'!F14","v":0}]
[null,null,null,null,"Service",{"$f":"'Step 1 - Project Information'!F15","v":0}]
[null,null,null,null,"Freight Performance",{"$f":"'Step 1 - Project Information'!F16","v":0}]
[]
[]
[null,null,3,"Please complete the Guidance Analysis below to define the intent of the project. The summary of your results is listed here: "]
[null,null,null,null,"G1, Signal Control",{"$f":"CCTV!G17","v":"NOT SUPPORTED"},null,null,null,"signal timing"]
[null,null,null,null,"G2, Traffic Incident",{"$f":"CCTV!G18","v":"NOT SUPPORTED"},null,null,null,"incident management if fully supported; 25% of benefits if partially supported"]
[null,null,null,null,"G3, Weather Verification",{"$f":"CCTV!G19","v":"NOT SUPPORTED"},null,null,null,"incident management if fully supported; 25% of benefits if partially supported"]
[null,null,null,null,"G4, Traveler Information",{"$f":"CCTV!G20","v":"NOT SUPPORTED"},null,null,null,"incident management if fully supported; 25% of benefits if partially supported"]
[null,null,null,null,"G5, Field Device Verification",{"$f":"CCTV!G21","v":"NOT SUPPORTED"},null,null,null,"productivity"]
[null,null,null,null,"G6, Workzone",{"$f":"CCTV!G22","v":"NOT SUPPORTED"},null,null,null,"work zone"]
[]
[]
[null,null,"Safety Benefits (W1, W2, W3, W6)"]
[]
[null,null,"S2. ","How many crashes, by severity, occurred in the past year at this intersection?"]
[null,null,null,null,null,{"$f":"CCTV!H90","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"CCTV!H91","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"CCTV!H92","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"CCTV!H93","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"CCTV!H94","v":0},"Property Damage-Only"]
[]
[null,null,"S2. ","Guidance on Treatment of the Economic Value of Statistical Life in the US (2019) estimates plus 1.6% (2020), 7.0% (2021) the consumer price index, as per the US Bureau of Labor Statistics in the MN region (1). "]
[null,null,null,null,null,{"$f":"PARAMETERS!E7","v":13000000},"Fatal Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E8","v":1235728},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E9","v":243152},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E10","v":116168},"Possible Injury"]
[null,null,null,null,null,{"$f":"PARAMETERS!E11","v":5200},"Property Damage-Only"]
[]
[null,null,"S3 (G1). ","Average crash reduction due to signal retiming (3)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E15","v":31},"percent"]
[]
[null,null,"S3 (G2, G3, G6). ","Average percent of crashes that are secondary crashes (5)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E17","v":1},"percent"]
[null,null,"S3.1 (G2, G3, G6).","Average percent reduction of secondary crashes (5)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E18","v":40},"percent",null,null,"*found to reduce secondary crashes by 30 - 50 percent. We have taken an average. "]
[]
[null,null,"S4 (G1). ","Average theoretical number of reduced crashes at proposed intersection per severity type after signal re-timing."]
[null,null,null,null,null,{"$f":"F33*(($F$47/100))","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"F34*(($F$47/100))","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"F35*(($F$47/100))","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"F36*(($F$47/100))","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"F37*(($F$47/100))","v":0},"Property Damage-Only"]
[]
[null,null,"S5 (G1). ","Expected safety benefit per crash severity type."]
[null,null,null,null,null,{"$f":"(F55)*F40","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"(F56)*F41","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"(F57)*F42","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"(F58)*F43","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"(F59)*F44","v":0},"Property Damage-Only"]
[]
[null,null,"S5 (G2, G3, G6). ","Expected safety benefit per crash severity type."]
[null,null,null,null,null,{"$f":"F33*($F$50/100)*($F$52/100)*F40","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"F34*($F$50/100)*($F$52/100)*F41","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"F35*($F$50/100)*($F$52/100)*F42","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"F36*($F$50/100)*($F$52/100)*F43","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"F37*($F$50/100)*($F$52/100)*F44","v":0},"Property Damage-Only"]
[]
[null,null,null,null,"Estimated Annual Safety Benefit (G1):",{"$f":"IF(AND(SUM(F62:F66)>0,NOT(F22=\"NOT SUPPORTED\")),ROUND(SUM(F62:F66),-3),0)","v":0}]
[null,null,null,null,"Estimated Annual Safety Benefit (G2, G3, G6):",{"$f":"IF(AND(SUM(F69:F73)>0,OR(F23=\"SUPPORTED\",F24=\"SUPPORTED\",F27=\"SUPPORTED\")),ROUND(SUM(F69:F73),-3),IF(AND(SUM(F69:F73)>0,OR(F23=\"PARTIALLY SUPPORTED\",F24=\"PARTIALLY SUPPORTED\",F27=\"SUPPORTED\")),ROUND(SUM(F69:F73)*0.25,-3),0))","v":0}]
[]
[]
[null,null,"Mobility Benefits (G1, G2, G3, G4, G6)"]
[]
[null,null,"M1 (G1). ","What is the estimated ADT for  all vehicles entering the intersection?"]
[null,null,null,null,null,{"$f":"CCTV!H102","v":0},"hours per AM or PM peak"]
[]
[null,null,"M2 (G1).","What is the average Relative Need at this intersection according to the Needs Assessment Tool - Service preset?"]
[null,null,null,null,null,{"$f":"CCTV!H105","v":0}]
[]
[null,null,"M3 (G1).","Average delay per vehicle at current Relative Need (s):",null,{"$f":"VLOOKUP(F85,'DROP-DOWNS'!$AP$2:$AQ$5,2,0)","v":{"$error":"#N/A"}},"sec"]
[]
[null,null,"M4 (G1).","Average delay reduction due to signal retiming (3).",null,{"$f":"PARAMETERS!E26","v":27.2},"percent reduction"]
[]
[null,null,"M5 (G1).","Average delay per vehicle at future LOS (s).",null,{"$f":"F87-F89/100*F87","v":{"$error":"#N/A"}},"sec"]
[]
[null,null,"M6 (G1).","Average delay reduction per vehicle (s).",null,{"$f":"F87-F91","v":{"$error":"#N/A"}},"sec"]
[]
[null,null,"M7 (G1).","Estimated volume impacted by delay reduction.",null,{"$f":"0.1*F82","v":0},"vehicles per day"]
[null,null,null,"Assumes 10% of ADT during worst hour."]
[]
[null,null,"M8 (G1).","Average vehicle occupancy (6).",null,{"$f":"PARAMETERS!E23","v":1.67},"persons per vehicle"]
[]
[null,null,"M9 (G1).","Estimated average travel time savings per year per vehicle. Estimation assumes travel time savings is predominate during peak period weekday times."]
[null,null,null,null,null,{"$f":"F95*5*52*F93/3600","v":{"$error":"#N/A"}},"vehicle hours per year",null,null," veh/day * days/week * weeks/year * s * 1 hr/3600 s"]
[]
[null,null,"M10 (G1).","Value of Travel Time Savings per FHWA (7)",null,{"$f":"PARAMETERS!E24","v":20.384000000000004},"per hour TTS"]
[]
[null,null,null,null,"Estimated Annual Mobility Benefit (G1):",{"$f":"IF(F22=\"SUPPORTED\",ROUND((F103)*F101*F98,-3),0)","v":0}]
[]
[null,null,"M1 (W2, W3, W4, W6).","Estimate the average number of traffic events that occur per year within site distance of the proposed camera."]
[null,null,null,null,null,{"$f":"CCTV!H108","v":0},"events per year"]
[]
[null,null,"M2 (W2, W3, W4, W6).","Estimate the average duration (minutes) of the traffic events that occur within site distance of the proposed camera."]
[null,null,null,null,null,{"$f":"CCTV!H111","v":0},"minutes"]
[]
[null,null,"M3 (W2, W3, W4, W6).","Provide the current AADT along the corridor where the proposed camera will be deployed (the Needs Analysis Tool may be used to obtain the value)."]
[null,null,null,null,null,{"$f":"CCTV!H102","v":0},"veh per day"]
[]
[null,null,"M4 (W2, W3, W4, W6).","Average percent reduced incident duration due to incident management (5)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E28","v":37.25},"percent reduction"]
[]
[null,null,"M5 (W2, W3, W4, W6).","Average reduced incident duration due to incident management per event."]
[null,null,null,null,null,{"$f":"F111*(F117/100)","v":0},"min per incident"]
[]
[null,null,"M6 (W2, W3, W4, W6).","Average number of vehicles positively affected by the reduction of incident duration (i.e. number of vehicles that can be expected to be within the corridor during the reduced duration period) (15):",null,null,null,null,null,"*Assumes that 80% of traffic will occur in 17 hours of the day based on average traffic distribution provided in source."]
[null,null,null,null,null,{"$f":"(F120/60)*F114*0.8/17","v":0},"vehicles per incident"]
[]
[null,null,"M7 (W2, W3, W4, W6). ","Average vehicle occupancy (6).",null,{"$f":"[1]PARAMETERS!J8","v":1.67},"persons per vehicle"]
[]
[null,null,"M8 (W2, W3, W4, W6). ","Average total time savings per vehicle due to incident management in this location per year."]
[null,null,null,null,null,{"$f":"(F123*F120*F108)/60","v":0},"hours per year"]
[]
[null,null,"M9 (W2, W3, W4, W6). ","Value of Travel Time Savings per FHWA (7).",null,{"$f":"[1]PARAMETERS!J9","v":19.459447999999998},"per hour TTS"]
[]
[null,null,null,null,"Estimated Annual Mobility Benefit (G2, G3, G4, G6):",{"$f":"IF(OR(F23=\"SUPPORTED\",F24=\"SUPPORTED\",F25=\"SUPPORTED\",F27=\"SUPPORTED\"),ROUND(F128*F130*F125,-3),IF(OR(F23=\"SUPPORTED\",F24=\"SUPPORTED\",F25=\"SUPPORTED\",F27=\"SUPPORTED\"), ROUND(F128*F130*F125*0.25,-3),0))","v":0}]
[]
[]
[null,null,null,null,"Estimated Annual Mobility Benefit (G1):",{"$f":"F105","v":0}]
[null,null,null,null,"Estimated Annual Mobility Benefit (G2, G3, G4, G6):",{"$f":"F132","v":0}]
[]
[]
[null,null,"Productivity Benefits (W5)"]
[]
[null,null,"P1. ","It is assumed that productivity benefits will be realized through reduced maintenance efforts. Estimate for how long maintenance efforts have been increasing at the proposed device replacement location(s). "]
[null,null,null,null,null,{"$f":"CCTV!H119","v":0}]
[null,null,null,null,null,{"$f":"IF(F142=\"3 years\",3,IF(F142=\"6 months\",0.5,1))","v":1}]
[]
[null,null,"P2. ","How many Cartegraph tickets have been required at this location over the length of time indicated above in P1? (if request is for multiple intersections, include cumulative total here)"]
[null,null,null,null,null,{"$f":"CCTV!H121","v":0},"tickets"]
[null,null,"P3. ","What was the total cost of these tickets?"]
[null,null,null,null,null,{"$f":"CCTV!H123","v":0}]
[]
[null,null,"P8. ","What is the anticipated percent reduction of maintenance tickets due to the proposed project?"]
[null,null,null,null,null,{"$f":"CCTV!H126","v":0},"percent"]
[null,null,null,null,null,{"$f":"VLOOKUP(F151,'DROP-DOWNS'!AC2:AD7,2,0)","v":{"$error":"#N/A"}}]
[]
[null,null,null,null,"Estimated Annual Productivity Benefit:",{"$f":"IF(F26=\"NOT SUPPORTED\",0,IF(F26=\"SUPPORTED\",(F148/F143)*(F152/100),IF(F26=\"PARTIAL SUPPORTED\",0.25*((F148/F143)*(F152/100)),0)))","v":0}]
[]
[]
[null,null,"Energy and Environment Benefits (G1, G2, G3, G4, G6)"]
[]
[null,null,"E1. ","Estimated average travel time savings per year. Estimation assumes travel time savings is predominate during peak period weekday times."]
[null,null,null,null,"G1 TTS",{"$f":"IF(F22=\"SUPPORTED\",F101,IF(F22=\"PARTIALLY SUPPORTED\",0.25*F101,0))","v":0},"hours per year"]
[null,null,null,null,"G2, G3, G4, G6 TTS",{"$f":"IF(OR(F23=\"SUPPORTED\",F24=\"SUPPORTED\",F25=\"SUPPORTED\",F27=\"SUPPORTED\"),F128,IF(OR(F23=\"PARTIALLY SUPPORTED\",F24=\"PARTIALLY SUPPORTED\",F25=\"PARTIALLY SUPPORTED\",F27=\"PARTIALLY SUPPORTED\"),0.25*F128,0))","v":0},"hours per year"]
[null,null,null,null,"Total TTS",{"$f":"F161+F160","v":0},"hours per year"]
[]
[null,null,"E2. ","Average fuel consumption per minute of idle time (18). It is assumed that the reduction in fuel consumption would be based primarily on the reduction of idle time. This is conservative because there would be fuel savings due to the smoother traffic progression."]
[null,null,null,null,null,{"$f":"PARAMETERS!E34","v":6.4999999999999997E-3},"gal / min"]
[]
[null,null,"E3. ","Average fuel consumption reduction per year.",null,{"$f":"F165*F162*60","v":0},"gallons / year"]
[]
[null,null,"E4. ","Average Wisconsin fuel cost (12).",null,{"$f":"PARAMETERS!E33","v":3.31},"$ per gallon"]
[]
[null,null,null,null,"Energy and Environment Benefit Subtotal:",{"$f":"F169*F167","v":0}]
[]
[null,null,"E5. ","Average CO2 emitted per gallon of gasoline burned (6).",null,{"$f":"PARAMETERS!E35","v":8.9200000000000008E-3},"metric tons per gallon"]
[]
[null,null,"E6. ","Average CO2 emission reduction due to the proposed project.",null,{"$f":"F173*F167","v":0},"metric tons"]
[]
[null,null,"E7. ","Average cost per metric ton of CO2 (16).",null,{"$f":"PARAMETERS!E36","v":228},"$ per metric ton"]
[]
[null,null,null,null,"Energy and Environment Benefit Subtotal:",{"$f":"F177*F175","v":0}]
[]
[null,null,null,null,"Energy and Environment Benefit:",{"$f":"IF(SUM(F135:F136)>0,F179+F171,0)","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,"Y1","Annual Benefit",{"$f":"F75+F76+F135+F136+F154+F181","v":0},"Y6","PV Annual Benefit",{"$f":"E198/(1.07^1)","v":0}]
[null,null,"Y1","Estimated Cost",{"$f":"CCTV!G11","v":0},"Y6","PV Estimated Cost",{"$f":"E190/(1.07^5)","v":0}]
[]
[null,null,"Y2","PV Annual Benefit",{"$f":"E186/(1.07^1)","v":0},"Y7","PV Annual Benefit",{"$f":"H186/(1.07^1)","v":0}]
[null,null,"Y2","PV Estimated Cost",{"$f":"(PARAMETERS!E46*CCTV!G14)/(1.07^1)","v":0},"Y7","PV Estimated Cost",{"$f":"E190/(1.07^6)","v":0}]
[]
[null,null,"Y3","PV Annual Benefit",{"$f":"E189/(1.07^1)","v":0},"Y8","PV Annual Benefit",{"$f":"H189/(1.07^1)","v":0}]
[null,null,"Y3","PV Estimated Cost",{"$f":"E190/(1.07^2)","v":0},"Y8","PV Estimated Cost",{"$f":"E190/(1.07^7)","v":0}]
[]
[null,null,"Y4","PV Annual Benefit",{"$f":"E192/(1.07^1)","v":0},"Y9","PV Annual Benefit",{"$f":"H192/(1.07^1)","v":0}]
[null,null,"Y4","PV Estimated Cost",{"$f":"E190/(1.07^3)","v":0},"Y9","PV Estimated Cost",{"$f":"E190/(1.07^8)","v":0}]
[]
[null,null,"Y5","PV Annual Benefit",{"$f":"E195/(1.07^1)","v":0},"Y10","PV Annual Benefit",{"$f":"H195/(1.07^1)","v":0}]
[null,null,"Y5","PV Estimated Cost",{"$f":"E190/(1.07^4)","v":0},"Y10","PV Estimated Cost",{"$f":"E190/(1.07^9)","v":0}]
[]
[null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"SUM(E186,E189,E192,E195,E198,H186,H189,H192,H195,H198)","v":0}]
[null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"SUM(E187,E190,E193,E196,E199,H187,H190,H193,H196,H199)","v":0}]
[]
[null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"G201/G202","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,"Return-on-Investment =",{"$f":"(G201-G202)/G202","v":{"$error":"#DIV/0!"}}]

{"sheet":"TRAWS","index":10,"n_rows":61,"n_cols":9,"rows_kept":61,"cut":false,"hidden":true,"merged":[[21,6,21,7],[24,4,24,7],[60,7,60,8],[10,6,10,7],[13,6,13,7],[16,6,16,7],[17,6,17,7],[18,6,18,7],[19,6,19,7],[20,6,20,7],[15,4,15,5],[12,4,12,7],[9,4,9,7],[1,2,2,2],[1,3,1,8],[2,3,2,8],[3,3,3,6],[6,6,6,7],[5,6,5,7],[4,6,4,7]]}
[]
[null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,"Project Benefit-Cost Analysis - TRAWS"]
[]
[null,null,null,null,null,"Region:",{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,null,null,"Proposed Project Name:",{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,null,null,"Proposed Project Benefit Analyst:",{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[]
[null,null,null,1,"What is the expected design and present value construction cost (total project cost)?"]
[]
[]
[null,null,null,2,"What is the anticipated number of TRAWS sites to be installed for the project?"]
[]
[]
[null,null,null,3,"Provide the anticipated level of need in the vicinity of the proposed project using the following Needs Analysis Tool presets:"]
[null,null,null,null,null,"Default TIP",{"$f":"IF('Step 1 - Project Information'!F11=0,\"-\",'Step 1 - Project Information'!F11)","v":"-"}]
[null,null,null,null,null,"Safety",{"$f":"IF('Step 1 - Project Information'!F12=0,\"-\",'Step 1 - Project Information'!F12)","v":"-"}]
[null,null,null,null,null,"Mobility (Present)",{"$f":"IF('Step 1 - Project Information'!F13=0,\"-\",'Step 1 - Project Information'!F13)","v":"-"}]
[null,null,null,null,null,"Mobility (Future)",{"$f":"IF('Step 1 - Project Information'!F14=0,\"-\",'Step 1 - Project Information'!F14)","v":"-"}]
[null,null,null,null,null,"Service",{"$f":"IF('Step 1 - Project Information'!F15=0,\"-\",'Step 1 - Project Information'!F15)","v":"-"}]
[null,null,null,null,null,"Freight Performance",{"$f":"IF('Step 1 - Project Information'!F16=0,\"-\",'Step 1 - Project Information'!F16)","v":"-"}]
[]
[null,null,"Safety Benefits"]
[null,null,null,"S1.","How many crashes, by severity, occurred in the past year at this intersection or corridor? If using the Needs Analysis Tool to obtain crash data, divide the 5 year crashes by 5 to provide an average annual number of crashes."]
[null,null,null,null,null,null,null,"Fatal Injury"]
[null,null,null,null,null,null,null,"Suspected Serious Injury"]
[null,null,null,null,null,null,null,"Suspected Minor Injury"]
[null,null,null,null,null,null,null,"Possible Injury"]
[null,null,null,null,null,null,null,"Property Damage-Only"]
[]
[null,null,null,null,null,"Estimated Annual Safety Benefit:",{"$f":"'TRAWS Calc'!F56","v":0}]
[]
[]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[]
[null,null,null,"Y1","Annual Benefit",{"$f":"'TRAWS Calc'!E60","v":0},"Y6","PV Annual Benefit",{"$f":"'TRAWS Calc'!$H$60","v":0}]
[null,null,null,"Y1","Estimated Cost",{"$f":"'TRAWS Calc'!E61","v":0},"Y6","PV Estimated Cost",{"$f":"'TRAWS Calc'!$H$61","v":0}]
[]
[null,null,null,"Y2","PV Annual Benefit",{"$f":"'TRAWS Calc'!E63","v":0},"Y7","PV Annual Benefit",{"$f":"'TRAWS Calc'!$H$63","v":0}]
[null,null,null,"Y2","PV Estimated Cost",{"$f":"'TRAWS Calc'!E64","v":0},"Y7","PV Estimated Cost",{"$f":"'TRAWS Calc'!$H$64","v":0}]
[]
[null,null,null,"Y3","PV Annual Benefit",{"$f":"'TRAWS Calc'!E66","v":0},"Y8","PV Annual Benefit",{"$f":"'TRAWS Calc'!$H$66","v":0}]
[null,null,null,"Y3","PV Estimated Cost",{"$f":"'TRAWS Calc'!E67","v":0},"Y8","PV Estimated Cost",{"$f":"'TRAWS Calc'!$H$67","v":0}]
[]
[null,null,null,"Y4","PV Annual Benefit",{"$f":"'TRAWS Calc'!E69","v":0},"Y9","PV Annual Benefit",{"$f":"'TRAWS Calc'!$H$69","v":0}]
[null,null,null,"Y4","PV Estimated Cost",{"$f":"'TRAWS Calc'!E70","v":0},"Y9","PV Estimated Cost",{"$f":"'TRAWS Calc'!$H$70","v":0}]
[]
[null,null,null,"Y5","PV Annual Benefit",{"$f":"'TRAWS Calc'!E72","v":0},"Y10","PV Annual Benefit",{"$f":"'TRAWS Calc'!$H$72","v":0}]
[null,null,null,"Y5","PV Estimated Cost",{"$f":"'TRAWS Calc'!E73","v":0},"Y10","PV Estimated Cost",{"$f":"'TRAWS Calc'!$H$73","v":0}]
[]
[]
[null,null,null,null,null,null,"10-Year Monetized Benefits =",{"$f":"'TRAWS Calc'!G76","v":0}]
[null,null,null,null,null,null,"10-Year Estimated Costs =",{"$f":"'TRAWS Calc'!G77","v":0}]
[]
[null,null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"'TRAWS Calc'!G79","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,null,"Return-on-Investment =",{"$f":"'TRAWS Calc'!G80","v":{"$error":"#DIV/0!"}}]
[]
[null,null,null,null,null,null,null,"Back to Links Tab"]

{"sheet":"TRAWS Calc","index":11,"n_rows":80,"n_cols":8,"rows_kept":80,"cut":false,"hidden":true,"merged":[[16,5,16,6],[17,5,17,6],[24,3,24,5],[31,3,31,5],[38,3,38,5],[1,3,1,7],[75,6,75,7],[76,6,76,7],[0,3,0,6],[41,3,41,5],[48,3,48,5],[5,5,5,6],[4,5,4,6],[6,5,6,6],[9,3,9,4],[9,5,9,6],[11,3,11,6],[12,5,12,6],[13,5,13,6],[14,5,14,6],[15,5,15,6]]}
[null,null,null,{"$f":"DMS!E2","v":"Transportation System Management and Operations - Traffic Infrastructure Process"}]
[null,null,null,{"$f":"TRAWS!D3","v":"Project Benefit-Cost Analysis - TRAWS"}]
[]
[]
[null,null,null,null,"Region:",{"$f":"'Step 1 - Project Information'!E5","v":0}]
[null,null,null,null,"Proposed Project Name:",{"$f":"'Step 1 - Project Information'!E6","v":0}]
[null,null,null,null,"Requested By:",{"$f":"'Step 1 - Project Information'!E7","v":0}]
[]
[]
[null,null,1,"What is the anticipated cost of the project?",null,{"$f":"TRAWS!G11","v":0}]
[]
[null,null,2,"Provide the anticipated level of need in the vicinity of the proposed project using the following Needs Analysis Tool presets:"]
[null,null,null,null,"Default TIP",{"$f":"'Step 1 - Project Information'!F11","v":0}]
[null,null,null,null,"Safety",{"$f":"'Step 1 - Project Information'!F12","v":0}]
[null,null,null,null,"Mobility (Present)",{"$f":"'Step 1 - Project Information'!F13","v":0}]
[null,null,null,null,"Mobility (Future)",{"$f":"'Step 1 - Project Information'!F14","v":0}]
[null,null,null,null,"Service",{"$f":"'Step 1 - Project Information'!F15","v":0}]
[null,null,null,null,"Freight Performance",{"$f":"'Step 1 - Project Information'!F16","v":0}]
[]
[]
[]
[]
[null,null,"Safety Benefits "]
[]
[null,null,"S1. ","How many crashes, by severity, occurred in the past year at this intersection?"]
[null,null,null,null,null,{"$f":"TRAWS!G26","v":0},"Fatal Injury"]
[null,null,null,null,null,{"$f":"TRAWS!G27","v":0},"Suspected Serious Injury"]
[null,null,null,null,null,{"$f":"TRAWS!G28","v":0},"Suspected Minor Injury"]
[null,null,null,null,null,{"$f":"TRAWS!G29","v":0},"Possible Injury"]
[null,null,null,null,null,{"$f":"TRAWS!G30","v":0},"Property Damage-Only"]
[]
[null,null,"S2. ","Guidance on Treatment of the Economic Value of Statistical Life in the US (2019) estimates plus 1.6% (2020), 7.0% (2021) the consumer price index, as per the US Bureau of Labor Statistics in the MN region (1)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E7","v":13000000},"Fatal Crashes"]
[null,null,null,null,null,{"$f":"PARAMETERS!E8","v":1235728},"Incapacitating Injury Crashes"]
[null,null,null,null,null,{"$f":"PARAMETERS!E9","v":243152},"Non-incapacitating Injury Crashes"]
[null,null,null,null,null,{"$f":"PARAMETERS!E10","v":116168},"Possible Injury Crashes"]
[null,null,null,null,null,{"$f":"PARAMETERS!E11","v":5200},"Property Damage Only Crashes"]
[]
[null,null,"S3. ","Average crash reduction due to TRAWS (17)."]
[null,null,null,null,null,{"$f":"PARAMETERS!E19","v":28},"percent"]
[]
[null,null,"S4. ","Average theoretical number of crashes reduced at proposed location per severity type after TRAWS deployment:"]
[null,null,null,null,null,{"$f":"F26*($F$40/100)","v":0},"Fatal Crashes"]
[null,null,null,null,null,{"$f":"F27*($F$40/100)","v":0},"Incapacitating Injury Crashes"]
[null,null,null,null,null,0,"Non-incapacitating Injury Crashes"]
[null,null,null,null,null,0,"Possible Injury Crashes"]
[null,null,null,null,null,0,"Property Damage Only Crashes"]
[]
[null,null,"S5. ","Expected safety benefit per crash severity type."]
[null,null,null,null,null,{"$f":"F43*F33","v":0},"Fatal Crashes"]
[null,null,null,null,null,{"$f":"F44*F34","v":0},"Incapacitating Injury Crashes"]
[null,null,null,null,null,0,"Non-incapacitating Injury Crashes"]
[null,null,null,null,null,0,"Possible Injury Crashes"]
[null,null,null,null,null,0,"Property Damage Only Crashes"]
[]
[null,null,null,null,"Estimated Annual Safety Benefit:",{"$f":"SUM(F50:F54)","v":0}]
[]
[]
[null,null,"Benefit-Cost Analysis"]
[null,null,"Y1","Annual Benefit",{"$f":"F56","v":0},"Y6","PV Annual Benefit",{"$f":"E72/(1.07^1)","v":0}]
[null,null,"Y1","Estimated Cost",{"$f":"TRAWS!G11","v":0},"Y6","PV Estimated Cost",{"$f":"E73/(1.07^1)","v":0}]
[]
[null,null,"Y2","PV Annual Benefit",{"$f":"E60/(1.07^1)","v":0},"Y7","PV Annual Benefit",{"$f":"H60/(1.07^1)","v":0}]
[null,null,"Y2","PV Estimated Cost",{"$f":"(PARAMETERS!E47*TRAWS!G14)/(1.07^1)","v":0},"Y7","PV Estimated Cost",{"$f":"H61/(1.07^1)","v":0}]
[]
[null,null,"Y3","PV Annual Benefit",{"$f":"E63/(1.07^1)","v":0},"Y8","PV Annual Benefit",{"$f":"H63/(1.07^1)","v":0}]
[null,null,"Y3","PV Estimated Cost",{"$f":"E64/(1.07^1)","v":0},"Y8","PV Estimated Cost",{"$f":"H64/(1.07^1)","v":0}]
[]
[null,null,"Y4","PV Annual Benefit",{"$f":"E66/(1.07^1)","v":0},"Y9","PV Annual Benefit",{"$f":"H66/(1.07^1)","v":0}]
[null,null,"Y4","PV Estimated Cost",{"$f":"E67/(1.07^1)","v":0},"Y9","PV Estimated Cost",{"$f":"H67/(1.07^1)","v":0}]
[]
[null,null,"Y5","PV Annual Benefit",{"$f":"E69/(1.07^1)","v":0},"Y10","PV Annual Benefit",{"$f":"H69/(1.07^1)","v":0}]
[null,null,"Y5","PV Estimated Cost",{"$f":"E70/(1.07^1)","v":0},"Y10","PV Estimated Cost",{"$f":"H70/(1.07^1)","v":0}]
[]
[]
[null,null,null,null,null,"20-Year Monetized Benefits =",{"$f":"SUM(E60,E63,E66,E69,E72,H60,H63,H66,H69,H72)","v":0}]
[null,null,null,null,null,"20-Year Estimated Costs =",{"$f":"SUM(E61,E64,E67,E70,E73,H61,H64,H67,H70,H73)","v":0}]
[]
[null,null,null,null,null,"Benefit-Cost Ratio =",{"$f":"G76/G77","v":{"$error":"#DIV/0!"}},":1"]
[null,null,null,null,null,"Return-on-Investment =",{"$f":"(G76-G77)/G77","v":{"$error":"#DIV/0!"}}]

{"sheet":"Other","index":12,"n_rows":44,"n_cols":6,"rows_kept":44,"cut":false,"hidden":true,"merged":[[2,3,2,5],[21,3,21,5],[27,3,27,5],[22,3,22,5],[4,4,4,5],[5,4,5,5],[6,4,6,5],[8,4,8,5],[10,3,10,5],[28,3,28,5],[33,3,33,5],[34,3,34,5],[39,3,39,5],[40,3,40,5]]}
[]
[null,null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,null,"Project Benefits - Other"]
[]
[null,null,null,"Region:",{"$f":"IF('Step 1 - Project Information'!E5=0, \"-\",'Step 1 - Project Information'!E5)","v":"-"}]
[null,null,null,"Proposed Project Name:",{"$f":"IF('Step 1 - Project Information'!E6=0, \"-\",'Step 1 - Project Information'!E6)","v":"-"}]
[null,null,null,"Proposed Project Benefit Analyst:",{"$f":"IF('Step 1 - Project Information'!E7=0, \"-\",'Step 1 - Project Information'!E7)","v":"-"}]
[]
[null,null,1,"What is the expected design and construction cost (total project cost)?"]
[]
[null,null,2,"Provide the anticipated level of need in the vicinity of the proposed project using the following Needs Analysis Tool presets:"]
[null,null,null,null,"Default TIP",{"$f":"IF('Step 1 - Project Information'!F11=0,\"-\",'Step 1 - Project Information'!F11)","v":"-"}]
[null,null,null,null,"Safety",{"$f":"IF('Step 1 - Project Information'!F12=0,\"-\",'Step 1 - Project Information'!F12)","v":"-"}]
[null,null,null,null,"Mobility (Present)",{"$f":"IF('Step 1 - Project Information'!F13=0,\"-\",'Step 1 - Project Information'!F13)","v":"-"}]
[null,null,null,null,"Mobility (Future)",{"$f":"IF('Step 1 - Project Information'!F14=0,\"-\",'Step 1 - Project Information'!F14)","v":"-"}]
[null,null,null,null,"Service",{"$f":"IF('Step 1 - Project Information'!F15=0,\"-\",'Step 1 - Project Information'!F15)","v":"-"}]
[null,null,null,null,"Freight Performance",{"$f":"IF('Step 1 - Project Information'!F16=0,\"-\",'Step 1 - Project Information'!F16)","v":"-"}]
[]
[]
[null,null,"Safety Benefits"]
[]
[null,null,"S1. ","Describe the anticipated Safety benefits of the proposed project."]
[]
[]
[]
[null,null,"Mobility Benefits"]
[]
[null,null,"M1. ","Describe the anticipated Mobility benefits of the proposed project."]
[]
[]
[]
[null,null,"Productivity Benefits"]
[]
[null,null,"P1. ","Describe the anticipated Productivity benefits of the proposed project."]
[]
[]
[]
[null,null,"Energy and Environment Benefits"]
[]
[null,null,"E1. ","Describe the anticipated Energy and Environment benefits of the proposed project."]
[]
[]
[]
[null,null,null,null,null,"Back to Links Tab"]

{"sheet":"PARAMETERS","index":13,"n_rows":77,"n_cols":9,"rows_kept":77,"cut":false,"hidden":false,"merged":[[6,2,10,2],[11,2,12,2],[47,2,49,2],[6,7,10,7]]}
[]
[null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,"Project Benefit-Cost Analysis - Parameters"]
[]
[null,null,"Safety Parameters"]
[null,null,"Source","Description","Value",null,null,"Comments"]
[null,null,"Guidance on Treatment of the Economic Value of Statistical Life in the US (2019) estimates plus 1.6% (2020), 7.0% (2021) the consumer price index, as per the US Bureau of Labor Statistics in the MN region (1).","Fatal Injury",{"$f":"12500000*D77","v":13000000},null,null,"Updated based on 2024 Benefit Cost analysis for discretionary grants"]
[null,null,null,"Suspected Serious Injury",{"$f":"1188200*D77","v":1235728}]
[null,null,null,"Suspected Minor Injury",{"$f":"233800*D77","v":243152}]
[null,null,null,"Possible Injury",{"$f":"111700*D77","v":116168}]
[null,null,null,"Property Damage-Only",{"$f":"5000*D77","v":5200}]
[null,null,"Average Crash Rate per MEV (2).","Rural Crash Rate (per MEV)",0.96]
[null,null,null,"Urban Crash Rate (per MEV)",1.05]
[null,null,"Average crash reduction due to DMS (14).","percent reduction",8.6]
[null,null,"Average crash reduction due to signal retiming (3).","percent reduction",31]
[null,null,"Average crash reduction along a coordinated corridor (4).","percent reduction",10.5]
[null,null,"Average percent of crashes that are secondary crashes (5).","percent crashes",1,null,null,"Updated using 2017-2020 data from 10 states in the USDOT report Secondary Crash Research: A Multistate analysis June 2023"]
[null,null,"Average percent reduction of secondary crashes by CCTV (5).","percent reduction",{"$f":"(30+50)/2","v":40}]
[null,null,"Average crash reduction due to TRAWS (17).","percent reduction",28]
[]
[null,null,"Mobility Parameters"]
[null,null,"Source","Description","Value"]
[null,null,"Average vehicle occupancy (6).","persons per vehicle",1.67]
[null,null,"Value of Travel Time Savings per FHWA and estimates an increase in inflation in 2023 (19)","$ per hour TTS",{"$f":"19.6*D77","v":20.384000000000004},null,null,"Updated based on 2024 Benefit Cost analysis for discretionary grants"]
[null,null,"Per capita income in past 12 months (7).","$ per year",{"$f":"40130*D77","v":41735.200000000004},null,null,"Updated based on census data."]
[null,null,"Average delay reduction due to signal retiming (8).","percent reduction",27.2]
[null,null,"Approximate percentage of travelers who will adjust their travel route based on information provided by DMS (9).","percent",18,null,null,"A 2023 Utah study reported 18% diversion - https://rosap.ntl.bts.gov/view/dot/72910\n\nFuture updates should adjust this figure to be delay/travel time reduction dependent. If there are not viable parallel routes where travel time reductions are likely then DMS will not help. For example, the MDOT 2022 study on Quantifying Effectiveness and Impacts of Digital Message Signs on Traffic Flow reported an average diversion rate of 3.6% with a 35% increase in diversion per 1 min of travel time savings. Other studies have also shown that queuing and congestion significantly affects the diversion rate.\nhttps://www.michigan.gov/MDOT/-/Media/Project/Websites/MDOT/Programs/Research-Administration/Final-Reports/SPR-1709-Report.pdf\n"]
[null,null,"Average percent reduced incident duration due to incident management (10).","percent reduction",37.25]
[null,null,"Approximate percentage of travelers who consult 511 AND change route (11).","percent",{"$f":"(9*49)/100","v":4.41},null,null,"Not currently used in workbook"]
[]
[null,null,"Environmental Parameters"]
[null,null,"Source","Description","Value"]
[null,null,"Average Wisconsin fuel cost (AAA Gas prices 03/01/2022) (12).","Fuel cost ($ per gallon)",3.31,null,null,"Updated based on July 2024 AAA gas prices"]
[null,null,"Average fuel consumption per minute of idle time. It is assumed that the reduction in fuel consumption would be based primarily on the reduction of idle time. This is a low estimate of fuel savings because there would be additional savings due to the smoother traffic progression (13).","Fuel consumption per idle time (gallons / min)",6.4999999999999997E-3,null,null,"Updated based on .39 gal per hour with no load from ford crown victoria from ANL 1 & 2 data from Argonne National Lab conventional vehicle snapshot - 2012 and 2013"]
[null,null,"Average CO2 emitted per gallon of gasoline burned (6).","Metric tons per gallon",8.9200000000000008E-3]
[null,null,"Average cost per metric ton of C02 (6).","$ per metric ton",228,null,null,"Updated based on table A-6"]
[]
[null,null,"Productivity Parameters"]
[null,null,"Source","Item","Cost"]
[null,null,"TOAMS?","Cellular Capital Cost",2500]
[null,null,null,"Cellular Monthly\nCharge per Location",150]
[null,null,"TOAMS / Todd Assumed per mile per pair of fiber per year.","Average Revenue per Mile Fiber",300,null,null,"Updated based on email from Todd on 7/3/2024"]
[null,null,null,"Average Annual Cost of Fiber Pair Leased Per Mile",600]
[null,null,null,"Annual O&M Cost for Mile Fiber",10,null,null,null,"PM"]
[null,null,"TOAMS","Annual O&M Cost for DMS",{"$f":"908+1840","v":2748},null,null,"The average annual electricity cost of DMS is $908. Based on FY 24 data, the average work order cost across all DMS is $1,840. Based on FY 22 data from TAPCO report, the average repair cost is $773 and average preventative maintenance cost is $267.",{"$f":"60720/227","v":267.48898678414099}]
[null,null,"TOAMS","Annual O&M Cost for Camera",{"$f":"707+1450","v":2157},null,null,"The average annual electricity cost of CCTVs is $707. Based on FY 24 data, the average work order cost across all CCTV is $1,450. Based on FY 22 data from TAPCO report, the average repair cost is $590.84 and average preventative maintenance costs is $300",{"$f":"162632/543","v":299.50644567219155}]
[null,null,"TOAMS / Don. assume DMS, 2 detectors, and 1 CCTV used for a TRAWS site.","Annual O&M Cost for TRAWS",{"$f":"3.6*350","v":1260},null,null,"The average annual electricity cost of TRAWS is $350. Assumed to be similar to DMS and Camera for 3.6x electrical costs"]
[null,null,"AASHTO Salary Survey","IT hourly wage",{"$f":"60777/(52*40)","v":29.219711538461539}]
[null,null,null,"Electrical Tech hourly wage",{"$f":"60777/(52*40)","v":29.219711538461539},null,null,"NETWORK COMMUNICATIONS SYSTEMS ANALYST ADVANCED - $60,777 annually"]
[null,null,null,"Civil Engineer hourly wage",{"$f":"84365/(52*40)","v":40.560096153846153},null,null,"$84,365 annually"]
[null,null,"Tapco Email (2014)","Emergency Surcharge (Tapco email)",100]
[]
[null,null,"Ratio for Conversion to 2023 Dollars (8)"]
[null,null,"Year","Inflation Factor"]
[null,null,2000,1.77]
[null,null,2001,1.72]
[null,null,2002,1.69]
[null,null,2003,1.66]
[null,null,2004,1.61]
[null,null,2005,1.56]
[null,null,2006,1.51]
[null,null,2007,1.47]
[null,null,2008,1.42]
[null,null,2009,1.42]
[null,null,2010,1.4]
[null,null,2011,1.35]
[null,null,2012,1.33]
[null,null,2013,1.31]
[null,null,2014,1.29]
[null,null,2015,1.29]
[null,null,2016,1.27]
[null,null,2017,1.24]
[null,null,2018,1.21]
[null,null,2019,1.19]
[null,null,2020,1.18]
[null,null,2021,1.1200000000000001]
[null,null,2022,1.04]

{"sheet":"REFERENCES","index":14,"n_rows":61,"n_cols":4,"rows_kept":61,"cut":false,"hidden":false,"merged":[[5,2,7,2],[8,2,10,2],[11,2,13,2],[14,2,16,2],[17,2,19,2],[20,2,22,2],[23,2,25,2],[26,2,28,2],[29,2,31,2],[32,2,33,2],[49,2,51,2],[52,2,54,2],[55,2,57,2],[34,2,36,2],[37,2,39,2],[40,2,42,2],[43,2,45,2],[46,2,48,2]]}
[]
[null,null,"Transportation System Management and Operations - Traffic Infrastructure Process"]
[null,null,"Project Benefit-Cost Analysis - Parameters"]
[]
[null,null,"References"]
[null,null,1,"https://www.transportation.gov/sites/dot.gov/files/2023-12/Benefit%20Cost%20Analysis%20Guidance%202024%20Update.pdf"]
[null,null,null,"Benefit Cost Analysis for Discretionary Grants December 2023 (page 39)"]
[null,null,null,"2024-07-01"]
[null,null,2,"https://www.fhwa.dot.gov/tpm/guidance/avo_factors.pdf"]
[null,null,null,"Geometric Categories as Intersection Safety Evaluation Tools (page 3)"]
[null,null,null,2005]
[null,null,3,"https://www.itskrs.its.dot.gov/sites/default/files/docs/The%20Benefits%20of%20Retiming%20Traffic%20Signals%20%28ITE%20Journal%29.pdf"]
[null,null,null,"The Benefits of Retiming Traffic Signals (page 18)"]
[null,null,null,"2004-04-01"]
[null,null,4,"http://www.fhwa.dot.gov/publications/researc/safety/04091/08.cfm"]
[null,null,null,"Signalized Intersections: Informational Guide (page 19)"]
[null,null,null,"2012-04-12"]
[null,null,5,"https://ops.fhwa.dot.gov/publications/fhwahop23043/fhwahop23043.pdf"]
[null,null,null,"Secondary Crash Research: A Multistate Analysis "]
[null,null,null,"2024-07-01"]
[null,null,6,"https://www.transportation.gov/sites/dot.gov/files/2023-12/Benefit%20Cost%20Analysis%20Guidance%202024%20Update.pdf"]
[null,null,null,"Benefit Cost Analysis for Discretionary Grants December 2023 (page 41)"]
[null,null,null,"2024-07-01"]
[null,null,7,"https://www.census.gov/quickfacts/WI"]
[null,null,null,"Per Capita Income - Wisconsin"]
[null,null,null,"2024-07-01"]
[null,null,8,"https://www.minneapolisfed.org/about-us/monetary-policy/inflation-calculator"]
[null,null,null,"CPI-U US Inflation Rate"]
[null,null,null,"2024-07-01"]
[null,null,9,"https://rosap.ntl.bts.gov/view/dot/72910"]
[]
[]
[null,null,10,"RITA ITS Benefits or Virginia 511 services"]
[null,null,null,"2004-01-01"]
[null,null,11,"http://ntl.bts.gov/lib//jpodocs/repts_te/13987.html"]
[null,null,null,"In Virginia, an evaluation of 511 services indicated 90 percent of callers found the service useful, and nearly half adjusted their travel plans based on the information provided."]
[null,null,null,"2004-11-01"]
[null,null,12,"https://gasprices.aaa.com/?state=WI"]
[null,null,null,"Average Wisconsin fuel cost"]
[null,null,null,"2024-07-01"]
[null,null,13,"https://www.anl.gov/esia/idle-reduction-information"]
[null,null,null,"Idle Fuel Consumption for Selected Gasoline Diesel Vehicles"]
[null,null,null,"2024-07-01"]
[null,null,14,"https://rosap.ntl.bts.gov/view/dot/29148"]
[null,null,null,"Costs and Benefits of MDOT Intelligent Transportation System Deployments (page 156)"]
[null,null,null,"2015-07-13"]
[null,null,15,"http://www.fhwa.dot.gov/policyinformation/pubs/hf/pl11028/chapter4.cfm"]
[null,null,null,"Highway Finance Data Collection (page 5)"]
[null,null,null,"2014-11-07"]
[null,null,16,"http://www.forbes.com/2009/06/03/cap-and-trade-intelligent-investing-carbon.html"]
[null,null,null,"Calculating the True Cost of Carbon"]
[null,null,null,"2009-06-03"]
[null,null,17,"https://www.fhwa.dot.gov/publications/research/safety/16035/16035.pdf"]
[null,null,null,"Safety Evaluation of Intertraffic Conflict Warning Systems (Page 5)"]
[null,null,null,"2022-06-16"]
[null,null,18,"https://www.energy.gov/eere/wipo/downloads/traffic-synchronization-and-management-energy-savings"]
[null,null,null,"Traffic Signal Synchronization for Energy Savings"]
[null,null,null,"2011-05-18"]
[null,null,19,"https://www.transportation.gov/sites/dot.gov/files/2023-12/Benefit%20Cost%20Analysis%20Guidance%202024%20Update.pdf"]
[null,null,null,"Benefit Cost Analysis for Discretionary Grants December 2023 (page 40)"]
[null,null,null,"2024-07-01"]

{"sheet":"DROP-DOWNS","index":15,"n_rows":7,"n_cols":51,"rows_kept":7,"cut":false,"hidden":true,"merged":[[0,28,0,29],[0,37,0,38]]}
["YES.NO",null,"ITS Device Project Purpose",null,"Signal Project Purpose",null,"Signal Replace Type",null,"Region",null,"Region2",null,"3 Project Type",null,"Expansion Type",null,"Expansion Purpose",null,"Software Upgrade",null,"Urban or Rural",null,"Increased Maintenance",null,"Signal Retrofit Type",null,"Vehicles Entering",null,"Percent Ticket Reduction",null,null,"Travel Time Reduction",null,"Location Type",null,"ITS Device Type",null,"Work Order Avg Ticket Amount",null,"#",null,"Needs Identification","Corresponding Delay (s)",null,"LOS","Corresponding Delay (s)",null,null,"Link Importance",null,"Detection Types"]
["YES",null,"Enhanced Traveler Information",null,"Increase Vehicular Safety",null,"Geometric Changes",null,"BTO",null,"Southeast",null,"Signal Retrofit",null,"Wireless/Cellular to Fiber Expansion",null,"Establish Signal Communications",null,"Next Generation Upgrade",null,"Urban",null,"6 months",null,"Monotubes",null,"> 2,000 vehs entering the avg intersection",null,"1 - 5",{"$f":"AVERAGE(1,5)","v":3},null,"Slight",null,"Intersection",null,"CCTV",null,"BTO",1253,211,null,"LOW",10,null,"A",5,null,null,"Critical"]
["NO",null,"Enhanced Incident Management",null,"Decrease Maintenance Efforts",null,"Addition of Turn Lane(s)",null,"Southeast",null,"Southwest",null,"LED Signal Replacement",null,"Communications Backbone",null,"Eliminate Necessity for Service Provider",null,"Current Software Replacement",null,"Rural",null,"1 year",null,"Flashing Yellow Arrows",null,"< 2,000 vehs entering the avg intersection",null,"6 - 15",{"$f":"AVERAGE(6,15)","v":10.5},null,"Moderate",null,"Non-Intersection",null,"DMS",null,"Southeast",316,1048,null,"MEDIUM",35,null,"B",15,null,null,"Somewhat Critical",null,"Microwave Detection"]
["PARTIAL",null,null,null,null,null,"Cabinet Adjustments",null,"Southwest",null,"Northeast",null,null,null,null,null,null,null,"New Software Deployment",null,null,null,"3 years",null,"Minor Safety Improvements",null,null,null,"16 - 30",{"$f":"AVERAGE(16,30)","v":23},null,"Significant",null,null,null,"Detection",null,"Southwest",482,218,null,"HIGH",75,null,"C",27.5,null,null,"Important",null,"Bluetooth Detection"]
[null,null,null,null,null,null,"Other",null,"Northeast",null,"North Central",null,null,null,null,null,null,null,null,null,null,null,null,null,"Adaptive Signal System",null,null,null,"31 - 50",{"$f":"AVERAGE(31,50)","v":40.5},null,null,null,null,null,"Other",null,"Northeast",437,86,null,"N/A",{"$f":"NA()","v":{"$error":"#N/A"}},null,"D",45,null,null,null,null,"Probe Data"]
[null,null,null,null,null,null,null,null,"North Central",null,"Northwest",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"51 - 75",{"$f":"AVERAGE(51,75)","v":63},null,null,null,null,null,null,null,"North Central",444,23,null,null,null,null,"E",67.5,null,null,null,null,"Thermal Detection"]
[null,null,null,null,null,null,null,null,"Northwest",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"76 - 95",{"$f":"AVERAGE(76,95)","v":85.5},null,null,null,null,null,null,null,"Northwest",733,104,null,null,null,null,"F",85,null,null,null,null,"Radar Detection"]